Retrivieving the Max value From a Table on the Many to One Side

[JHMarshIII]

I am trying to retrive the record that has the latest expiration date for inclusion on a report or for display in a form using a query to pull the related expiration date from the Many to One side of a relationship.

I have tried unsucessfully to use the MAX(fieldname) function. I keep getting an aggegrate error on the first field in the query.

I created a database to track memberships and due payments. I have two tables, Members and FinHist. They are joined on memberID. Each time a member pays his/hers dues, a new record is created in the FinHist table. The membership's most current exiration date should always be the latest record in the FinHist table. It is this record I awsays want to retrieve or display. What I am I doing wrong or what do I need to do?

 

[KKilfoil]

Create a summary query using FinHist as source.

Add your [memberID] field, and select 'Group By' in the totals row.

Add your [Expirationdate] (or whatever) field, and select 'Max' in the totals row.

Since this is a summary query, add no other fields unless you need to perform an aggregate function (such as 'Max') on them.