Finding names from table of not duplicated rows

[hooby1]

Please ignore the properties of the way the table is set up as this is a simple broken down copy and sample data of the table I am using to show as an example.

Is it possible to run a query to show the following:

I want to know any names who have done "FirstAid 2" and / or "FirstAidOS2" but not "FirstAid 3". There are other courses shown (FirstAidOS) which can be included. Is this a simple query to create?

So looking at the sample data I want to see in the end result query:

ID Start Course Surname FirstName Client
1 23-Apr-24 FirstAidOS s1 f1 C1
2 23-Apr-24 FirstAidOS s2 f2 D1
3 16-Apr-24 FirstAid 3 s3 f3 E1
4 23-Apr-24 FirstAidOS s4 f4 F1
5 23-Apr-24 FirstAidOS s5 f5 G1
6 17-Sep-24 FirstAid 2 s14 s14 J1
7 05-Sep-24 FirstAidOS s7 f7 L1
8 14-Mar-24 FirstAidOS2 s17 s17 M1
13 25-Jun-24 FirstAid 2 s12 s12 Q1
14 12-Nov-24 FirstAid 2 s8 f8 R1
15 23-Apr-24 FirstAidOS2 s15 s15 S1
16 09-Jul-24 FirstAidOS2 s16 s16 T1
17 05-Sep-24 FirstAidOS s13 s13 U1
20 05-Sep-24 FirstAidOS2 s11 s11 K1

because the following have done both I don't these to appear:

ID Start Course Surname FirstName Client
19 14-May-24 FirstAid 2 s10 s10 W1
18 17-Sep-24 FirstAid 3 s10 s10 W1
12 05-Sep-24 FirstAid 3 s6 f6 P1
11 14-May-24 FirstAid 2 s6 f6 P1
10 17-Sep-24 FirstAid 3 s9 f9 O1
9 14-May-24 FirstAid 2 s9 f9 O1

Not sure if this is too complex for an access query to find? I can't think of the query that can do this? Hope someone can help?

 

[Pat Hartman]

Your example can be done with two queries that are joined together in a third.

Query 1 selects
...
Where Course In ("FirstAid 2", "FirstAidOS2")

Query 2 selects
...
Where Course In("FirstAid 3")

Query 3 does a left join of query 1 to query 2
...
Where Course Is Null --- so it selects any person from query 1 that doesn't have an entry in query 2

If you prefer, you can use subqueries rather than separate querydefs.

 

[Gasman]

Is this what you want?

SELECT tblFirstAid.*
FROM tblFirstAid
WHERE (((tblFirstAid.Course)="FirstAid 2") AND ((tblFirstAid.Course)<>"FirstAid 3")) OR (((tblFirstAid.Course)="FirstAidOS2") AND ((tblFirstAid.Course)<>"FirstAid 3"));

 

[cheekybuddha]

SELECT
  Surname,
  FirstName
FROM
WHERE Course LIKE 'FirstAid*'
  AND Course <> 'FirstAid 3'
GROUP BY
  Surname,
  FirstName
HAVING COUNT(*) > 1
;

 

[Gasman]

SELECT
  Surname,
  FirstName
FROM
WHERE Course LIKE 'FirstAid*'
  AND Course <> 'FirstAid 3'
GROUP BY
  Surname,
  FirstName
HAVING COUNT(*) > 1
;

That is going to pull in FirstAidOS ? which was not requested?

Edit:
However it does not pull in anything?

SELECT  Surname,  FirstName FROM tblfirstaid
WHERE Course LIKE 'FirstAid*'  AND Course <> 'FirstAid 3'
GROUP BY   Surname,   FirstName
HAVING COUNT(*) > 1

 

[cheekybuddha]

That is going to pull in FirstAidOS ? which was not requested?

Thanks, missed that

Just change the where clause to be more specific:

-- ...
WHERE (Course = 'FirstAid 2' OR Course = 'FirstAidOS2')
  AND Course <> 'FirstAid 3'
GROUP BY
  Surname,
  FirstName
HAVING COUNT(*) >= 1
-- ...