General discussion

Locked

MS Access - TOUGH ONE

By MRA2 ·
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

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by john.a.wills In reply to MS Access - TOUGH ONE

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

Collapse -

by MRA2 In reply to

THANKS!!!!

Collapse -

by Alpha48 In reply to MS Access - TOUGH ONE

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

Collapse -

by MRA2 In reply to
Collapse -

by MRA2 In reply to MS Access - TOUGH ONE

This question was closed by the author

Back to Software Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums