Discussion Locked

Access 2000 Only if all meet criteria

0 helpful
4 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have an MS Access 2000 Database. Fields are Name, Year, Amount Collected. I want to find those name who have an Entry for Year 2002, Year 2003, & Year 2004 and a zero amount in all three.
Each Year is another record, of course.

If a member has only 2003 and 2004 records, or only 2004 or only 2002 or 2002 & 2004 I don't want them chosen.

Anyone have any idea how to do this????

Thanks,
Alan

It's

4 replies

by john_wills

SELECT for the 3 years and zero amount then SELECT double INNER JOIN on the results of the 3 lower-level SELECTs. Alternatively, do a UNION on 3 SELECTs by year and zero amount and select on the UNION for all 3 years being present.

by john_wills

Let me improve on the above. Have 3 SELECTS, one for each year WHERE amount non-zero. SELECT on the 3 with INNER JOIN.

by mra2

Your Commment was AWESOME & EASY!!!! Got it working in minutes!! Thanks so much!!

Alan
abramowitza@coned.com

This discussion is closed to new replies.