"Set Query" with "Not in" Condition 2

[JohnHarkins]

If I have a list of all contributions, I'd like to find out who has _not_ contributed in a given date-range who did contribute in a broader date-range. For example, how do I find the user IDs of contributors in the last three years who did _not_ contribute in the year 2000 (i.e., at the beginning, middle or end of the broader date-range)?

I assume this is a set or group query, but when I look up "set" in the index it doesn't show up. The other "trick" is the "not in" syntax required.

Thanks,

John John Harkins

 

[tlbroadbent]

You can accomplish what you desire using a query with a correlated subquery.

Select ContID, ContName
From Contributions a
Where ContDate >= dateadd(yy,-3,getdate())
And Not Exists
(Select ContID
From Contributions b
Where b.ContID=a.ContID
And ContDate >= dateadd(yy,-1,getdate()))

The subquery selects rows where contributions were made in the past year. The outer query selects rows where contributions were made in the past three years. The "Not Exists" clause further restricts the outer query selection to rows that don't exist in the inner query.

You might say we search for records in the set "Contributors in Past Three Years" that are not in the set "Contributors in the Past Year." The SQL language is just a little different. Terry

X-) "I don't have a solution, but I admire your problem."

 

[tlbroadbent]

Whoops! s-)

SQL Server and Access syntax are similar but not quite the same. Modify the dateadd function references to conform to Access syntax.

dateadd("yyyy",-1,date())

Sorry about that.
Terry

X-) "I don't have a solution, but I admire your problem."