Software

Ask something
Discussion Locked

MS Access - TOUGH ONE

0 helpful
5 replies

by mra2

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

I have a Parent Table with Names & Addresses. I also have a child table with Yearly Dues information. The child table has the year and an activity field (Active/Inactive).

For each year I enter the year and whether the person was active or inactive. Some people do not have any entry for every year!

I need a query which will find those Members who have EVERY CHILD record marked as INACTIVE.

For Example:
Member: Joe
2004 Active 2003 Inactive 2002 Active (Don't Choose this one)

Member: Sue
2004 Inactive 2003 Inactive 2002 Inactive (Choose this one)

Member: Rocky
2004 Inactive 2003 Inactive NO 2002 Record (Choose this one)

Member: Fred
2004 Active 2003 Active NO 2002 Record (Don't Choose this One)

I realize this is a really tough Query but I'm hoping that someone out there can help me....

Thanks,
Alan
NYC

5 replies

by john.a.wills

SELECT SSN as ThisSSN
WHERE NOT EXISTS (SELECT SSN WHERE SSN=ThisSSN and Flag <> "Inactive" FROM Child)
FROM Parent;

by alpha48

Alan, hopefully you did not over simplify the example you posted, because if you are just looking for the 2004 active members...just create a query with both [!] tables and in the query [in design view] pull down as many identifiable fields you need. Include and go to the 2004 field and enter the criteria Like "Acti*" This will group any text entry beginning with the letters "Acti". It will create the group of entries you need. If you included in your design grid, the years 2003 and 2002 the text for that membership will show as an added plus. Want to go further...drop the tools menu to export to Excel and sort further.

I hope this helps till something easier comes in.

Regards. Alpha48

This discussion is closed to new replies.