QUERY RESULTS MULTIPLE RECORDS FOR ONE PERSON

[netrusher]

I enter personnel data into a table through a form. The same person may have multiple records in the table. With each record there are yes/no boxes to be checked if that person is verified at a certain station. Let's say PERSON A has six records in the table. Let's say on one of those records he has StaPrimary yes/no box checked (or true).
I need a query to do the following: My query is to find out if any person listed in the table does not have any StaPrimary yes/no boxes checked. My dilemma is when I do the query if Person A has six records and one of the records has the StaPrimary yes/no box checked his name will still show up in the query because he has other recrods with the StaPrimary yes/no box not checked. I want to check all of each persons records in the table and if they have one StaPrimary yes/no box checked I do not want there name to show up in the query. How do I do that?

Thanks for your help

 

[lupins46]

select * from (Select distinct personid from mytable) as q1
left join (Select personid from mytable where staprimary = true) as q2
on q1.personid = q2.personid
where q2.personid is null

This will list the personid numbers. You need to use your own id field name and table name.

If you want more details of the person you will need to save this query and then use it in a new query with the person table and join on personid.

 

[netrusher]

Thanks but WHOA!

That is way over my head. Can that be explained down to my level?

Sorry for the lack of understanding.

 

[lupins46]

The query gets a list of all personids as a subquery.(q1)
It then gets a list of all personids with any staprimary = true as a second subquery.(q2)

It then finds all the personids in q1 which are not in q2 (which is those who do not have an record with staprimary = true)

 

[lupins46]

If you change the fieldnames and table to your own, you can drop the query string int the sql view of a new query (overwriting anything that is already there). You can then look at it in design view.

 

[PHV]

Another way:
SELECT PersonID FROM yourTable GROUP BY PersonID HAVING Min(StaPrimary) = 0

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886