Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2003
    Location
    Guelph, Canada
    Posts
    39

    Unanswered: Earliest dates in Query

    Hello,

    How would I be able to specify the criteria in a select query so that
    only the _earliest of records containing the same ID will be
    displayed?

    For example:

    John Doe Dec 21, 2002
    John Doe Dec 23, 2002
    John Doe Dec 24,2002
    Fred Flint Jan 12, 2003
    Fred Flint Jan 14, 2003
    Fred Flint Jan 15, 2003
    etc.

    For these records, pull up:

    John Doe Dec 21, 2002
    Fred Flint Jan 12, 2003
    etc.

    And while I'm asking, is it possible to do this with a select query of
    a select query? And if so, would the second query work its way through
    the first query automatically?

    Thanks very much! Eric

  2. #2
    Join Date
    Apr 2002
    Posts
    139
    You could add a calculated field to your query:

    Age: (Now()-[YourDate])

    Then use a Group By query to select the max value for Age

    hth

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you can do this with a correlated subselect query:
    Code:
    select recordkey
         , personname
         , somedate
      from yourtable X
     where somedate =
       ( select max(somedate)
           from yourtable
          where recordkey = X.recordkey )
    rudy
    http://r937.com/

  4. #4
    Join Date
    Jan 2003
    Location
    Guelph, Canada
    Posts
    39
    Hi Marion,

    Thanks for your reply. I like your idea.
    I tried it but it didn't work though.

    >> Age: (Now()-[YourDate])
    >> Then use a Group By query to select the max value for Age

    After running it, Access changed the calculated field to:

    Age: Max((Now()-[DateDet]))
    and changed the Total: to "Expression"

    The results:
    16.53...
    12.53...
    12.53...
    12.53...
    12.53...
    11.53...
    11.53...

    In other words the same as I was getting before, but with an additional field with number dates.
    I still get repeated dates. Thanks! Eric

  5. #5
    Join Date
    Apr 2002
    Posts
    139
    Hi Eric,

    Just re-created your table on this side to find the solution.
    Forget about the calculated field Age, it's simpler than that.

    Design a query,
    Include both your fields PersonName en PersonDate
    Switch to Group
    Set PersonDate on Min-value.

    That's all.

    Doing so, my query looks like:

    SELECT YourTable.PersonName, Min(YourTable.PersonDate) AS MinPersonDate
    FROM YourTable
    GROUP BY YourTable.PersonName;

    hth

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    marion, that only works when you are selecting the a grouping field plus the date

    it does not work when selecting the row that has the highest date

    consider this: suppose the table is clientname, depositdate, depositamount

    what was the amount of the last deposit for each client?

    you will need that correlated subquery

  7. #7
    Join Date
    Apr 2002
    Posts
    139
    Hi Rudy,

    Point taken!
    I will study the subquery topic.
    Might bring more elegant solutions when things get complicated.

    Thanks

    Arco

  8. #8
    Join Date
    Jan 2003
    Location
    Guelph, Canada
    Posts
    39
    Hi Rudy,

    I guess I'll have a stab at your code now, since the "easier" solutions haven't worked.

    select recordkey
    , personname
    , somedate
    from yourtable X
    where somedate =
    ( select max(somedate)
    from yourtable
    where recordkey = X.recordkey )

    Do I enter all of the above in the criteria field?
    Do I also use ,personname ,somedate ?
    They aren't comments? I've never seen commas used before.
    And if my table is named Detentions I'd put Detentions X ?

    Thanks! Eric

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Query > New > SQL View

    paste the query

    make sure you use your own table and column names

    yes, the X is important, it is the correlation variable


    rudy

  10. #10
    Join Date
    Jan 2003
    Location
    Guelph, Canada
    Posts
    39
    Hi Rudy,

    I tried out your code but had some problems.
    I get the following:

    Enter Parameter value:
    somedate
    recordkey
    x.recordkey

    I thought when I opened the query it would automatically find the records for me with the earliest dates.

    Thanks! Eric

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do you have a column called somedate? do you have a column called recordkey? i used those names as examples, because you did not say what the names of your columns are

    make sure you use your own table and column names


    rudy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •