Look for most recent date in sub table

  • Thread starter Thread starter Todd
  • Start date Start date
T

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!
 
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
 
Back
Top