Access Database SQL Joins 1

[Corusgroup]

I have three tables in an Access database. Number 1 holds details of users registered on our intranet system with UserRef as key field. Number 2 holds details of all course on our intranet system with CourseCode as the key field. A third table is related to the other 2 by a UserRef field to table 1 and a CourseCode field to table 2. Each record in this third table holds details of access and progress for a particular course, by an individual person.

For audit purposes, particularly on Health & Safety training, I need to find people registered on the system, who have not undertaken particular courses, i.e. a record does not exist in the third table for a person against a course. I have tried, unsuccessfully, to use a variety of inner, right and left joins, but nothing seems to get the result needed.

Does anybody have any ideas, or am I asking too much of the system.

Thanks in advance

 

[PHV]

A starting point:

SELECT A.UserRef, A.CourseCode
FROM (SELECT UserRef, CourseCode FROM [table 1], [table 2]
) AS A LEFT JOIN [table 3] AS B ON A.UserRef = B.UserRef AND A.CourseCode = B.CourseCode
WHERE B.UserRef Is Null

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Corusgroup]

Hi PH

Thanks for the quick response. I have attempted to integrate your solution, but as a member of the slow readers group I must be missing something.
To expand:
Table 1 (users) is called StudentData
Table 2 (courses) is called Courses
Table 3 (user/course progress) is called StudentCourses
and I have to read the result(s) into a record set, to produce a report where Courses are listed with details of users not accessing a course against it:
Course1:
User 1 Details
User 2 Details
Course2:
User 2 Details
and so on.

The SQL below is the nearest I have got to obtaining a result anywhere near that which I need, but it is still not there.

SELECT Courses.C_Code, Courses.C_Title, StudentData.StudentRef, StudentData.Name3, StudentData.Name1, StudentData.Name2, StudentData.Dept
FROM StudentData LEFT JOIN (Courses RIGHT JOIN StudentCourses ON Courses.C_Code=StudentCourses.C_Code) ON StudentData.StudentRef=StudentCourses.StudentRef
WHERE (((StudentData.localhremail)='name@company' AND StudentCourses.StudentRef IS NULL))
ORDER BY Courses.C_Code, StudentData.Name3, StudentData.Name1, StudentData.Name2;

My vain attempt at transposing your solution into our system was:

SELECT Courses.C_Code, Courses.C_Title, StudentData.StudentRef, StudentData.Name3, StudentData.Name1, StudentData.Name2, StudentData.Dept
FROM (SELECT StudentRef, C_Code FROM StudentData, Courses) LEFT JOIN StudentCourses ON StudentData.StudentRef = StudentCourses.StudentRef AND Courses.C_Code = StudentCourses.C_Code
WHERE StudentCourses.StudentRef IS NULL

Which comes up with a "Syntax error in JOIN operation.".

I have obviously misinterpreted something in the reply and hope I can be pointed in the right direction.

Thanks in advance.

Malc

 

[PHV]

A starting point:
SELECT A.C_Code, A.StudentRef
FROM (SELECT StudentRef, C_Code FROM StudentData, Courses
) AS A LEFT JOIN StudentCourses ON A.StudentRef = StudentCourses.StudentRef AND A.C_Code = StudentCourses.C_Code
WHERE StudentCourses.StudentRef IS NULL

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Corusgroup]

To PH

That's great; with a little manipulation I am now getting out the data I require, thanks for pointing me in the right direction.

We are Engineers and Trainers first, using IT as a tool and your help has been invaluable.

Much appreciated

Malc