Query for most recent contact with Customer

[Philly44]

I have db setup that has a lot of customers in it. I have been asked to setup a query based on some specific criteria, that part is easy. The problem I am having is with the date. My users want a query that will return the most recent contact with one of these customers.
Each Customer has a unique CustID and for each time they call they get assigned a new ContactID. I need in some way to tell the system when there are multiple contactID's to eliminate al but the most recent and display that in the query results. Any help would gratly appreciatd.

Thanx again

 

[JoeMiller]

If there's a date field you can use the MAX function to return the highest (aka most recent) call. Alternatively your ID MAY work if it's an autonumber that increments by 1, so try selecting MAX of your ID.

HTH Joe Miller
joe.miller@flotech.net

 

[jebry]

Hi!

Assuming the CustID is in the Contact table as a foriegn key you should be able to use aggregate functions to group on the CustID and for the date field use the aggregate function Max. This should return the record from the contact table for each CustID which has the latest value in the Date field.

hth
Jeff Bridgham

 

[Philly44]

Thanx to ya both you kicked my head in the right direction