Query to pull people with no activity prior to a certain date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to create a query to pull only people who have not had any activity
prior to Jan 1 of the prior year. The table with the list of names does not
keep track of when a name is added but the activity table does keep track of
the dates of any activity.
 
You can use something like the following although with a large number of
records, this can be slow.

SELECT *
FROM tblPeople
WHERE tblPeople.PK NOT IN
(Select PeopleID
FROM Activities
WHERE ActionDate < #1/1/2004#)

Two step solution - which is almost always faster in Access.
Create a query to get all the people that have had activities. Save that and
join it to the original table with an outer join

Save as queryA
Select PeopleID
FROM Activities
WHERE ActionDate < #1/1/2004#

Then use queryA in a second query based on your table and queryA
SELECT *
FROM tblPeople LEFT JOIN QueryA
 
Back
Top