selecting the right 'MAX' record

[KCMCS]

I have a table that I'm querying to extract out the 'max' record. The problem
is, there are some other variables in the table that I need to pull back with
it and when I include them, it messes with the results and gives me more than
one record.

Basically it is a table with the following fields:
FIELD NAME, DATA TYPE
a, number (this is the unique record id)
b, number (this is the field I need the max record for)
c, number (other record data)
d, datetime (other record data)
e, number (other record data)
f, varchar (other record data)

The value in field a could be in multiple records all pertaining to the same
'event'. What I need to get to is to pull out the single record pertaining to
the 'event' and based on it having the maximum number in field 'b', but I
also need to include the other fields in my query results. The problem being
that the other fields have varying data, so pulling back the records based
only on the max value in field 'b' returns multiple results because it also
looks at the other fields.

I hope this makes sense. Any help would be appreciated.

 

[KCMCS]

Here is an example of the data in the table:

a b c d e f
1 1 2 6/3/08 1 T
1 2 1 6/3/08 4 1
1 3 1 6/3/08 1 1
2 1 1 6/3/08 2 5
2 2 4 6/3/08 1 T


In the example above, I would want the query to return ONLY the record on
line 3 because field 'b' is '3' which is the max value of that field for all
the records pertaining to field 'a' which is '1', and also it should return
the record on line 5 because field 'b' is '2' (max value for that field for
all records pertaining to field 'a' which is '2').

 

[Michel Walsh]

You can take a look at: http://www.mvps.org/access/queries/qry0020.htm


Vanderghast, Access MVP

 

[Duane Hookom]

Check John Spencer's reply to the posting with the subject "Show latest
action for each customer". This was posted 6/2