Query that excludes entries from last year if they have registered for current year (1 Viewer)

stardustvega

Member
Local time
Today, 17:11
Joined
Feb 4, 2022
Messages
36
I am trying to run a query as a mail merge source. The purpose of this query is to pull up a list of anyone who sponsored an event last year, and to exclude from that list anyone who has already signed up to sponsor the event this year.

Essentially, I'm filtering to people who sponsored the event in 2021. All well and good. Where I'm struggling is figuring out how to exclude people who have registered for this year.

If I was doing it in Excel, I would do a simple COUNTIFS to count any instances of the company in the current row where the registered year is 2022, and then exclude any where the result was more than 1. But I'm struggling to figure out how to do the equivalent in access.

The SQL query so far is:

Code:
SELECT [J-Tbl BER Golf Companies].CustomerNr, [J-Tbl BER Golf Companies].GolfSponYear, [J-Tbl BER Golf Companies].GolfSpon, TblCompanies.BER_Member, TblCompanies.Original_Name, BER_Reps.BER_RepFirst, BER_Reps.BER_RepEmail, BER_Reps.BER_RepActive
FROM ([J-Tbl BER Golf Companies] INNER JOIN TblCompanies ON [J-Tbl BER Golf Companies].CustomerNr = TblCompanies.CustomerNr) INNER JOIN BER_Reps ON TblCompanies.CustomerNr = BER_Reps.CustomerNr
WHERE ((([J-Tbl BER Golf Companies].GolfSponYear)=2021) AND ((TblCompanies.BER_Member)=True) AND ((BER_Reps.BER_RepActive)=True));
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:11
Joined
Sep 21, 2011
Messages
14,047
Use a subquery for those this year and NOT IN.
Or possibly join the table again and use not = ?
 

plog

Banishment Pending
Local time
Today, 17:11
Joined
May 11, 2011
Messages
11,612
SELECT CustomerNr FROM YourTableHere GROUP BY CustomerNr HAVING MAX(GolfSponYear)=2021
 

stardustvega

Member
Local time
Today, 17:11
Joined
Feb 4, 2022
Messages
36
Oh, the subquery is probably perfect. I didn't know what terms to use to Google my problem in this case so I wasn't getting anything useful. Thanks!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:11
Joined
Feb 19, 2002
Messages
42,976
Some things require a sub query. Others can be done with joins. I use the QBE and its support for subqueries is non-existent so I use joins whenever I can. Also, Access doesn't optimize sub queries well so if you have a lot of data the join might be faster.

In this case you are joining the table to itself so add the table to the grid twice. Add criteria for the second instance:

Where Yourtable_1.GolfSponYear = 2021 and use a left join

Whoops. Just remembered, the left join won't work because of the criteria being on the right-side table, so you need to create a query for the criteria and then join the table to the query using a left join and criteria:

Where query.PK Is Null.

With minimum data, it doesn't matter whether you use the left join method or the sub query.
 

Users who are viewing this thread

Top Bottom