Web Development

Ask something
Discussion Locked

Access 97 Query Problem

0 helpful
6 replies

by mra2

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

Parent Table has Names $ Social Security Numbers.
Child Table has Social Security Numbers (Link) and a record for each year that person participated (2002 - 2001 - 2000)

I want to find those people who participated in 2002 ONLY (They have no 2001 Child record and no 2000 child record)

Can you help???

Thanks,
A2

6 replies

by jboardman

Create a query with both tables linked by the SSN. Configure the link so that ALL the Parent table records and ONLY the matching Child table records will show up. Put in whatever fields you want, but make sure you have added the 2001 and 2000 fieldswith criteria of Is Null or ="" for both fields. Add the 2002 field with criteria of Is Not Null or <>"" This should give you everyone who participated in 2002 & not in 2000 or 2001. If you need more details, you can contact me directly at jwboardman@celanese.com

by mra2

The Example.mdb that you Emailed me was FANTASTIC!!!! Thanks for all of your help!!!! MRA2@mindspring.com

by bob sellman

First, create a grouping query on Child that groups by SSN with a count of the number of records (count of year). I'll call that query qgrpYearsParticipated.

Create a second query where the Parent and Child tables are linked by the SSN and theremust be a match in both tables and the year column in Child must equal 2002, and also include the qgrpYearsParticipated linked (only records that are in both the Child table and the query) by SSN with the criteria of Count = 1.

The result will bethat the first part of the second query only lists matching records where the people participated in 2002. The second part only selects people who participated for one year, so since we're only displaying people who participated in 2002, if they only participated in one year then they are the ones who meet your criteria.

This discussion is closed to new replies.