I have a basic two table design: tblACCOUNTS and tblACTIONS. The tblACTIONS has these fields: Description, Date, Status, and Account. The latter is the link back to the tblACCOUNTS records.
tblACTIONS will have lots of records for each Account, but typically only a few with Status=Open and only one with Status=Open AND the most recent Date for that one Account.
I want to have two fields in the tblACCOUNTS that show me the latest Action record Description and Date, where these query/filter criteria apply (in pseudo SQL):
- FOR EACH Account
- WHERE Status=Open AND DATE = Latest
the result being my tblACCOUNTS shows not only my tblAccount native fields, but also the date and description of the “last action” (as defined in the above pseudo SQL) for that account on the one row.
Can I do this? How?
Tks.
