Grouping data

[Dave]

I am trying to group data for a specific event and have not been successful,
so I am hoping someone can help.

ID Date Value
174 12/3/2007 102
174 12/3/2007 909
174 12/11/2007 405
174 12/11/2007 701
206 12/6/2007 1001
173 12/3/2007 500

I want to group by ID, the MAX Date for that ID and the Max Value for the ID
& Max Date

The desired result would be:

ID Date Value
174 12/11/2007 701
206 12/6/2007 1001
173 12/3/2007 500

When I use GROUP and MAX the Value does not stay specific to an ID, I get
the MAX Value for ID, but not the Date and ID.

Not this

ID Date Value
174 12/11/2007 909
206 12/6/2007 1001
173 12/3/2007 500

I would appreciate any help.

Thanks

 

[Allen Browne]

See:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

If you have never worked with subqueries before, this may help also:
Subquery basics
at:
http://allenbrowne.com/subquery-01.html

 

[Dave]

Thanks for the quick response. I have used the queires from the example page
(http://www.mvps.org/access/queries/qry0020.htm) and substituted the following

BookID = PopID
BookDate = Date
BorrowerID = Value

I get multiple BookIDs listed in the result where I am looking for a single
BookID each time.

Make sense?

Dave

 

[Dave]

Got it!

I used a variation of an example on that page.

Thanks

Dave