Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Re: Date if then statement help!

Discussion in 'Information Technology' started by JudyT, Jul 28, 2006.

  1. JudyT

    JudyT
    Expand Collapse
    Guest

    Ken

    I know it has been a while this was posted but I recently found out that the
    query we built does not properly work...It does show the latest date they
    were unsat in a drill but I need to do a comparison of when they become sat
    in the next drill.

    In other words the query returns the latest date of when they were unsat but
    they can at a later date become sat thereby nullifying the first query. I
    have a query that shows those that were sat and the above showing unsat. How
    do I query on the one date that is the latest that is unsat but when they
    become sat they come off the list.

    Thank you for your help
    Judy

    "Ken Snell (MVP)" wrote:

    > You can continue to use Date as the field name, but you must always surround
    > it with [ ] characters so that it is clearly defined as a field. However,
    > you should change the field name to something else at your earliest
    > opportunity. By the way, the same "concern" applies to the use of Name as a
    > field name; it too is a reserved word.
    >
    > Using DrillID field as the unique identifier for the type of drill, this
    > query may be what you seek:
    >
    > SELECT [Individual Drill Performance Data].[Name],
    > [Individual Drill Performance Data].PERNR,
    > [Drill information].[Date]
    > FROM [Drill information] INNER JOIN
    > [Individual Drill Performance Data] ON
    > [Drill information].DrillID = [Individual Drill Performance Data].DrillID
    > WHERE [Drill information].[Date] =
    > (SELECT Max(T.[Date]) AS MTD
    > FROM [Drill information] AS T
    > WHERE T.DrillID = [Individual Drill Performance Data].DrillID);
    > WITH OWNERACCESS OPTION;
    >
    >
    > --
    >
    > Ken Snell
    > <MS ACCESS MVP>
    >
    >
    >
    >
    > "JudyT" <JudyT@discussions.microsoft.com> wrote in message
    > news:2681A2BD-00A4-4A3A-8556-F0D6D5F69950@microsoft.com...
    > > Hey Ken,
    > >
    > > Alright, from the beginning...this is a database that I have taken over.
    > > I
    > > will change the date issue but can we work around it for now? Here is the
    > > other info about the fields.
    > >
    > > [Individual Drill Performance Data].Name---Employee name--text type
    > > [Individual Drill Performance Data].PERNR---Employee Number-text type
    > > [Drill information].[Date]---The date the drill was held---short date type
    > > [Drill information].DrillID---Primary Key---the Identification of the
    > > drill---number type(which I know does not need to be this because there is
    > > no
    > > calculations done on this field.)INNER JOIN [Individual Drill Performance
    > > Data].DrillID---Foriegn key
    > >
    > >
    > > "Ken Snell (MVP)" wrote:
    > >
    > >> Not easily. I don't know what the different fields in the tables are. Can
    > >> you explain what these fields are:
    > >>
    > >> [Individual Drill Performance Data].Name
    > >> [Individual Drill Performance Data].PERNR
    > >> [Drill information].[Date]
    > >> [Drill information].DrillID
    > >> [Individual Drill Performance Data].DrillID
    > >>
    > >> Also, do not use Date as the names of a field. It is a reserved word in
    > >> ACCESS, and your use of it as a field name can lead to all kinds of
    > >> problems
    > >> and confusion. See these articles for more information:
    > >>
    > >> List of reserved words in Access 2002 and Access 2003
    > >> http://support.microsoft.com/default.aspx?scid=kb;en-us;286335
    > >>
    > >> List of Microsoft Jet 4.0 reserved words
    > >> http://support.microsoft.com/?id=321266
    > >>
    > >> Special characters that you must avoid when you work with Access
    > >> databases
    > >> http://support.microsoft.com/?id=826763
    > >>
    > >>
    > >> --
    > >>
    > >> Ken Snell
    > >> <MS ACCESS MVP>
    > >>
    > >>
    > >>
    > >> "JudyT" <JudyT@discussions.microsoft.com> wrote in message
    > >> news:233CB643-F8A3-4936-8D8B-DC27CA4159D0@microsoft.com...
    > >> > Ken,
    > >> >
    > >> > I am not sure how to use this select statement based on what I have.
    > >> > Here
    > >> > is what I have in my SQL view maybe it may work better if you plug
    > >> > inthe
    > >> > info.
    > >> >
    > >> > SELECT [Individual Drill Performance Data].Name, [Individual Drill
    > >> > Performance Data].PERNR, [Drill information].Date
    > >> > FROM [Drill information] INNER JOIN [Individual Drill Performance Data]
    > >> > ON
    > >> > [Drill information].DrillID = [Individual Drill Performance
    > >> > Data].DrillID
    > >> > WITH OWNERACCESS OPTION;
    > >> >
    > >> > "Ken Snell (MVP)" wrote:
    > >> >
    > >> >> Try this:
    > >> >>
    > >> >> SELECT * FROM Tablename
    > >> >> WHERE TestDateField =
    > >> >> (SELECT Max(T.TestDateField) AS MTD
    > >> >> FROM Tablename AS T
    > >> >> WHERE T.EmployeeNum = Tablename.EmployeeNum);
    > >> >>
    > >> >>
    > >> >> --
    > >> >>
    > >> >> Ken Snell
    > >> >> <MS ACCESS MVP>
    > >> >>
    > >> >>
    > >> >>
    > >> >> "JudyT" <JudyT@discussions.microsoft.com> wrote in message
    > >> >> news:0D1EDCE1-1B00-47B0-82A7-591797A77AD2@microsoft.com...
    > >> >> >I need help with a date based issue.
    > >> >> >
    > >> >> > I have an employee who takes a test on 01/01/2006 and then later
    > >> >> > that
    > >> >> > year
    > >> >> > that same person takes a similar test 02/02/2006. I am looking to
    > >> >> > do
    > >> >> > query
    > >> >> > that returns latest date (most recent). This is for many people
    > >> >> > within
    > >> >> > an
    > >> >> > organization who may have multiple different tests dates.
    > >> >> >
    > >> >> > JudyT
    > >> >>
    > >> >>
    > >> >>
    > >>
    > >>
    > >>

    >
    >
    >
     
  2. Loading...


Share This Page