[AccessD] Need most recent record from many

Didn't know about Max(datefield) and am excited about that, but I don't 
think I worded my question correctly, because both your's and Bob's 
suggestions make sense, but for the whole query.  I need each record on the 
one side to match up to one record on the many side (where I need the most 
recent record of a group).

Example - we have a group of members that have made dues payments for as 
few as one year (new member) up to an unlimited number of years and want to 
link only to the most recent year's dues information.  This group uses an 
expiration date to determine a current member, so it can cover more than 
one calendar year.  What I have been doing is sorting the information first 
by the primary key then descending by the received date and appending the 
information to a table with properties to only accept a unique primary 
key.  So, theoretically, the first record will be the most current and be 
the only one written to the table.  If this is the only way to work this 
situation, I'll set it up to execute whenever the db is opened and it will 
be static.  Not ideal, but if we know the limitations we can work with 
it.  I'd like to be able to access this information through a query and 
skip the append step and this is where I feel I must be missing 
something.  Hmmm...  If a store wanted to access only the most recent 
transaction for a certain customer say for billing purposes, how would they 
do that?  Grouping somehow and then using the Max function?

Thanks,
Susan

At 09:58 AM 3/28/03, you wrote:
>Susan,
>
>I assume you have a date field on the many side.  If so then
>Max(datefield) will get the latest date.
>
>Charles Wortz
>Software Development Division
>Texas Education Agency
>1701 N. Congress Ave
>Austin, TX 78701-1494
>512-463-9493
>CWortz at tea.state.tx.us