stardustvega
Member
- Local time
- Today, 16:18
- 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:
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));