Timeframe-specific most recent record selection

  • Thread starter Thread starter mvagnier
  • Start date Start date
M

mvagnier

Apologies right up front, as this is difficult to articulate and (therefore)
search on. If already addressed, sorry to ask again.

I have a table [table1] of people [memberID] and the date they joined our
program [enrolldate]. Members may visit any number of locations [site] as
often as they choose. A separate table [table2] tracks each visit in a record
containing [memberID], [site] and [visitdate].

I wish to list each [memberID] from [table1] and the most recent [visitdate]
and [site] from [table2], where the [visitdate] is no more than 2 months
after the [enrolldate].

Thanks in advance for considering this; I know there's a simple solution but
I can't see the forest for the trees.
 
UNTESTED UNTESTED --
SELECT [table1].[memberID], [table1].[enrolldate], [site], [visitdate]
FROM [table1] INNER JOIN [table2] ON [table1].[memberID] = [table2].[memberID]
WHERE (SELECT Max([tbl_X].[visitdate]) FROM [TABLE2] AS tbl_X WHERE
[table1].[memberID] = [tbl_X].[memberID]) <= DataAdd("m", 2, [enrolldate]);
 
Back
Top