Look for most recent date in sub table

[Todd]

tblContacts has a one to many relationship to tblNotes. The fields I am
working with are [Name] from tblContacts, [NoteDate] from tblNotes, and
[Note] from tblNotes. I need to show all contacts with their notes when all
of their [NoteDate]'s are older than 6 months. In other words, no one has
added a new note on the contact for 6 months or longer. Is this possible?
Thank you!

 

[Chris]

Sure.
Its the where clause that is tricky

You need something like ...

Where tblcontacts.primarykeyfield not in (select
Linkingtable.contactsPrimarykeyfield from Linkingtable
inner join tblNotes on linkingtable.notestableprimarykey =
tblnote.primarykeyfield where Notesdate > dateadd("m",-
6,now()))

it may pay to get the subquery - the bit in parenthesis -
working first before you use it in the where clause.
You should get a list of all contacts that HAVE a notes
record LESS than 6 month old. These will then be EXCLUDED
from the main query by the NOT IN part