query to extract dates where no rows contain a specific value

pma111
pma111 used Ask the Experts™
on
I need some assistance with an access query. I have a table, and there are 2 fields of interest, for now lets say Field9 (activity) and Field1 (date). the rows of data represent login activity to an application. The entries in Field9 can be either In, Out, Screensaver, Locked, Unlocked. What I need to do is to group on Field1 (date), to get a list of unique dates where none of the rows for that date contain the word Locked.

E.g. from this data:
Field1             Field9
25/09/2018   IN
25/09/2018   OUT
26/09/2018   IN
26/09/2018   LOCKED
26/09/2018   UNLOCKED
26/09/2018   OUT
27/09/2018   IN
27/09/2018   OUT

the query would return both 25/09/2018  and 27/09/2018 , as on both of those days, there was no locked entry in any of the rows for those dates, whereas for 26/09/2018 there was. Not sure what approach to use, I did think of group by count as a basis and manual filter of those results.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
You could try:

Select * 
From Table
Where Field1 Not In (Select Field1 From Table As T Where T.Field9 = "LOCKED")

Open in new window

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Another method, which might run quicker, depending on the size of your dataset would be:

SELECT yourTable.*
FROM yourTable
LEFT JOIN (
SELECT DISTINCT Field1 FROM yourTable WHERE Field9 = "Locked"
) as T ON yourTable.Field1 = T.Field1
WHERE T.Field1 IS NULL

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial