WHERE query

[Egg 'n' Bacon]

Hi I'll try to explain this as best I can.

I wish to create a report based on a query that compares results from multiple sources. The result should only show those records (from one source) that are NOT matched in the second source.

This is for analysis of training; actual training received against required.

I've created 3 queries; the 1st to identify the training received. The second to identify training required (for a particular role) and a third to combine the others.

It's in this 3rd query that I'm struggling.

1st query

Code: Select all

SELECT TblPerson.PersID, [FirstName] & " " & [LastName] AS Employee, tblPersonRoles.RendDate, TblPerson.LastName
FROM TblPerson INNER JOIN tblPersonRoles ON TblPerson.PersID = tblPersonRoles.PersID
WHERE (((tblPersonRoles.RendDate) Is Null))
ORDER BY TblPerson.LastName;

2nd query

Code: Select all

SELECT TblRole.RoleName, TblRole.RqdPersNos, Count(tblPersonRoles.PersID) AS CountOfPersID
FROM TblRole LEFT JOIN tblPersonRoles ON TblRole.RoleID=tblPersonRoles.RoleID
WHERE (((tblPersonRoles.RendDate) Is Null))
GROUP BY TblRole.RoleName, TblRole.RqdPersNos;

3rd query

Code: Select all

SELECT qryPersGAP1.PersID, qryPersGAP1.name, qryPersGAP2.CName
FROM qryPersGAP1, qryPersGAP2
WHERE (([qryPersGAP2]![CName]<>[qryPersGAP1]![CName]))
GROUP BY qryPersGAP1.PersID, qryPersGAP1.name, qryPersGAP2.CName;

N.B. I do realise that there is the same field (Cname) name in 2 separate tables

[HansV]

CName may be in two tables, but as far as I can tell it occurs in neither of the first two queries...

[Egg 'n' Bacon]

Oops, wrong queries.
Query one;

Code: Select all

SELECT TblPerson.PersID, [firstName] & " " & [lastName] AS name, TblCourse.CName
FROM TblPerson INNER JOIN (TblCourse INNER JOIN TblAttendance ON TblCourse.CID = TblAttendance.CourseID) ON TblPerson.PersID = TblAttendance.PersonID
GROUP BY TblPerson.PersID, [firstName] & " " & [lastName], TblCourse.CName
HAVING (((TblPerson.PersID) Like [forms]![frmperson]![persid]));

Query two

Code: Select all

SELECT TblCourse.CName, TblRole.RoleName
FROM TblRole INNER JOIN (TblCourse RIGHT JOIN TblCourseRole ON TblCourse.CID = TblCourseRole.CourseID) ON TblRole.RoleID = TblCourseRole.RoleID
WHERE (((TblRole.RoleName) Like [forms]![frmperson]![RoleName]));

[HansV]

Does this do what you want?

Code: Select all

SELECT qryPersGAP1.PersID, qryPersGAP1.name
FROM qryPersGAP1 LEFT JOIN qryPersGAP2 ON qryPersGAP1.CName= qryPersGAP2.CName
WHERE qryPersGAP2.CName Is Null
GROUP BY qryPersGAP1.PersID, qryPersGAP1.name;

[Egg 'n' Bacon]

Thank you Hans. Almost did it, but what you gave me was enough to finish it off.
Here's what I ended up with;

Code: Select all

SELECT qryPersGAP2.CName
FROM qryPersGAP1 RIGHT JOIN qryPersGAP2 ON qryPersGAP1.CName = qryPersGAP2.CName
WHERE (((qryPersGAP1.CName) Is Null))
GROUP BY qryPersGAP2.CName;

Very much appreciated