SELECT TOP 1 from detail table for each rec in master

[TraylorAssoc]

Hi All,

I'm creating a query and I need to be able to do a nested 'SELECT TOP 1' query to return a field from a detail table for each master record that the main query is selecting.

Here's the basic scenario:

Client Table:
ClientKey (Unique)
Fields(…)


Client Events Table:
ClientKey (Duplicates)
ClientEventKey (Unique)
Fields(…)


Client Events Detail Table:
ClientEventKey (Duplicates)
ClientEventDetailKey (Unique)
ClientEventDetailDate
ClientEventDetailDescKey (Duplicates)
Fields(…)


Client Event Detail Description Table:
ClientEventDetailDescKey (Unique)
ClientEventDetailDesc


The current query selects clients and one specific client event record for each client, as well as pulling in data from about 10 other lookup tables.

Now I also need to get the most recent record from the Client Events Detail table (based on ClientEventDetailDate) and return the associated ClientEventDetailDescKey from the Client Event Detail Description table.

This needs to occur for each record that the main query is returning and I’m totally stuck in getting this to work.

Any help or suggestions would be appreciated!

Thanks,
Tim