Most Recent Date

[tonyb]

I have 3 tables: ClientDetails, StatusLookUp and ClientStatusHistory linked. ClientStatusHistory has fields: ClientStatusHistoryID (Primary Key), ClientID (Foreign Key), StatusID (Foreign Key) and Date. This table records how Client's Status changes over time, hence the Date field being included.

I am having trouble writing a query that lists all the Clients showing their MOST RECENT status (i.e. their CURRENT status) and ignoring previous ones. I have tried the MAX function but when I include the Status field in the query I get ALL the statuses listed.

Thanks in advance...