Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Posts
    40

    Unanswered: Quer...How To Show Latest Date?

    Sorry, I should have started a new thread. Please ignore my last post. I have attached a small sample database. It contains a simple query "qryHighestScore" that contains three fields "MatchDate", "PlayerName" and "ThreeDartScore" When I run this query it returns 9 records (3 for each named player) I would like to modify the query a little further or build a new query that will return only 3 records (1 for each player) I have already done something similar to this with the help of TD from this forum using the Max option but this time I want the sort to be based on the new "MatchDate" field. For example, the ideal query would return only three records, based on the sample data those records would be:
    02/Jun/04 - Bob
    05/Apr/03 - Fred
    12/Feb/99 - Harry
    Many thanks in advance,
    Regards,
    Ronald A. Dixon
    Attached Files Attached Files

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    SELECT Max(Scores.MatchDate) AS MostRecent, Scores.PlayerName
    FROM Scores
    GROUP BY Scores.PlayerName;

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Aug 2004
    Posts
    40
    Thank you Izy, but can you (or anyone else for that matter) tell me why I now can't add the other field "ThreeDartScore" to this "newQrySolutionByIzy" without it reverting straight back to showing all 9 records again? I cannot understand this at all. I have attached the small sample database again to help illustrate the problem. You will see that the "newQrySolutionByIzy" query works great with only the two fields present, but I would like to see all three data fields present yet only 3 records returned. Is this possible? and if so, How can I do it?
    Thanks again,
    Regards,
    Ronald A. Dixon
    Attached Files Attached Files

  4. #4
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    You can add the other field "ThreeDartScore", but you can't use 'Group By' for the field otherwise you will get a record for each different "ThreeDartScore" because 'Group By' means 'group by each unique value'. If you want to use the field "ThreeDartScore" and only have each player show once, you will need to use something like 'Min', 'Max', 'Count', 'Sum' or 'Avg'.

    TD

  5. #5
    Join Date
    Jun 2009
    Location
    USA
    Posts
    1

    Quer...How To Show Latest Date?

    I have a table that contains two columns, Sample_ID and Test_Date.

    Each Sample_ID can be tested perodically over a period of months. Sample_ID is part of a finite set. I would like to be able to write a query such that I get a list of individual Sample ID's (no duplicates) and the most recent date they were tested... is this possible just using a query?.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You got an answer for this four years ago. Let me show u it
    Query by Latest Date? [Archive] - Access World Forums
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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