How do I select the last record of a group in MS Access

I have 2 tables.  The 1st is system and the second is actions.  The relationship is multiple records from the actions table for every record in the system table.  I want to run a query selecting all the data from the system table and only the last action from the action table.  I have a date field in the action table.  Is this possible? How can I do this?

Who is Participating?
calpurniaConnect With a Mentor Commented:
Hi Marilyn, you don't say much about the structure of your tables, so I'm making the following assumptions:

You have 2 tables called tblSystems and tblActions.

tblSystems has a primary key called SystemID.

tblActions has a date field called ActionDate, and a SystemID field

Alter the field and table names in the following SQL statement to match your database.

SELECT A.*,  S.* FROM tblActions A, tblSystems S
WHERE A.ActionDate IN (SELECT Max(A2.ActionDate) FROM tblActions A2 WHERE A2.SystemID=A.SystemID)
AND A.SystemID=S.SystemID;

Open in new window

if you have unique identifier for action table then set the "total" to Max
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.