Query Question

[Sue]

I suspect this is a beginner question...
I am using Access 2000
I have two linked tables that are tracking seed stocks. Table 1 has info on
the parents (it's fields are: ID#, number of seeds produced). Table 2 has
info on the offspring (it's fields are: ID#, seeds produced y/n, tissue
sample collected, link to parents).
I need to know parents where all offspring have no seed. And I'm trying to
design a query for that.
I can easily pick up parent IDs that have no tissue collection done because
the parent ID link doesn't get filled in for that. (so it's a find unmatched
query)
And I can pick up any offspring that have no seed.
the problem is that
there are many offspring for each parent... and some have had tissue
collection done that have not produced seed (so the unmatched query isn't
accurate).
so basically in my offspring table I have:
ID#, seeds produced y/n, tissue sample collected, link to parents
5-A No data 5
5-C Yes empty 5
9-A No empty none
16-B No data 16
The unmatched query does not pick this up parent 5 (good)
The no seed query picks up parent 5 (bad)
The no seed query does not pick up parent 9 (bad)
The unmatched query does not pick up parent 16 (bad)

I can trick the database by telling it to look for null and and that are -A
and ask the data entry people to enter the first offspring as always having
seed... but that screws up my seed tracking if we ever need to go back.

So how can I design a query that looks at sets of offspring records and only
returns data if none of the offspring has seeds?
Thanks.
-Sue

 

[tina]

it's always easier to ask Access what *is* there, rather than what *isn't*
there. try creating a query that returns every parent that has at least one
offspring record with seeds, i'll call it qryHasOffspringSeeds. then use
that query in an "unmatched" query, along with the parent table, to return
only those records in the parent table that *do not* have a matching record
in qryHasOffspringSeeds.

hth

 

[Sue]

Thanks, I knew it was something simple like that i just couldn't figure what.
I've followed your suggestion and it works now.
-Sue

 

[tina]

you're welcome
i was hoping one of the SQL experts would jump in; there's probably a more
elegant solution, perhaps utilizing a subquery, but SQL isn't my strongest
point...