Solved 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
-- ...

 

[XPS35]

My attempt:

SELECT Surname, FirstName, Client FROM tblFirstAid WHERE
Client IN (SELECT Client FROM tblFirstAid WHERE Course="FirstAid 2" OR Course="FirstAidOS2")
AND Client NOT IN (SELECT Client FROM tblFirstAid WHERE Course="FirstAid 3")

 

[cheekybuddha]

Edit:
However it does not pull in anything?

Note I changed the having clause to >= 1 in my amendment

Ps, I didn't test

 

[Gasman]

Yes, noted.
I just used the QBE.

 

[cheekybuddha]

Ps, I didn't test

I guess I should have tested

This should work:

SELECT
  t1.FirstName,
  t1.Surname
FROM tblFirstAid t1
WHERE t1.Course IN ('FirstAid 2', 'FirstAidOS2')
  AND NOT EXISTS (
   SELECT NULL
   FROM tblFirstAid t2
   WHERE t2.Course = 'FirstAid 3'
     AND t1.Surname = t2.Surname
     AND t1.FirstName = t2.FirstName
)
ORDER BY
  t1.Surname,
  t1.FirstName
;

 

[hooby1]

Hi all. Many thanks for all the replies. I will have a look at each one and see how I get on. It certainly does look like the queries will be the ones I require. Thank you so much for all your help and assistance.

 

[Pat Hartman]

@Gasman this cannot be done by a single query. It can be done using three separate queries as I described or if you are comfortable writing sub queries, it can be done using subqueries. The reason your method doesn't work is that course can never be more than a single value for a single record so any record is either going to be true for condition 1 or true for condition 2. It can NEVER be true for both. So, since the query is only looking at 1 record at a time, it can't determine if condition 1 is true AND condition 2 is false for a given person.

@cheekybuddha created a subquery version that should work.