CODEWITHSUNDEEP
sqlms-access
Robert Loughrey
Robert Loughrey

Reputation: 173

Query build to find records where all of a series of records have a value

Let me explain a little bit about what I am trying to do because I dont even know the vocab to use to ask. I have an Access 2016 database that records staff QA data. When a staff member misses a QA we assign a job aid that explains the process and they can optionally send back a worksheet showing they learned about what was missed. If they do all of these ina 3 month period they get a credit on their QA score. So I have a series of records all of whom have a date we assigned the work(RA1) and MAY have a work returned date(RC1). enter image description here

In the below image "lavalleer" has earned the credit because both of her sheets got returned. "maduncn" Did not earn the credit because he didn't do one. I want to create a query that returns to me only the people that are like "lavalleer". I tried hitting google and searched here and access.programmers.co.uk but I'm only coming up with instructions to use Not null statements. That wouldn't work for me because if I did a IS Not Null on "maduncn" I would get the 4 records but it would exclude the null.

What I need to do is build a query where I can see staff that have dates in ALL of their RC1 fields. If any of their RC1 fields are blank I dont want them to return.

Upvotes: 0

Views: 62

Answers (2)

June7
June7

Reputation: 21370

Consider:

SELECT * FROM tablename WHERE NOT UserLogin IN (SELECT UserLogin FROM tablename WHERE RCI IS NULL);

Upvotes: 1

Lee Mac
Lee Mac

Reputation: 16015

You could use a not exists clause with a correlated subquery, e.g.

select t.* from YourTable t where not exists 
(select 1 from YourTable u where t.userlogin = u.userlogin and u.rc1 is null)

Here, select 1 is used purely for optimisation - we don't care what the query returns, just that it has records (or doesn't have records).

Or, you could use a left join to exclude those users for which there is a null rc1 record, e.g.:

select t.* from YourTable t left join
(select u.userlogin from YourTable u where u.rc1 is null) v on t.userlogin = v.userlogin
where v.userlogin is null

In all of the above, change all occurrences of YourTable to the name of your table.

Upvotes: 0

Related Questions