All records from many-to-many table

[Mudface]

I have three tables, one for employee names, one for test methods, and one showing which employees have been trained on which test method. I would like a query to show all employees and all test methods (even where there has been no entry). The only way I've found to do this is to create one query, then another 'Cartesian' (is that the right word?) query based on the first one. Is there a better way to do this, and is it possible to make the final query updateable (not too important as I was just going to use this for a report)?

Tables:-
tblEmployees (EmployeeNo primary key)
tblTestMethods (TestID primary key)
tblTraining (many-to-one with both the above tables, EmployeeNo and TestID as composite primary key, Trained as a Yes/No field)

Queries:-
Query1 (to show all test methods). SQL-

SELECT tblTests.TestID, tblTraining.EmployeeNo, tblTraining.Trained
FROM tblTests LEFT JOIN tblTraining ON tblTests.TestID = tblTraining.TestID;

Query2 ('Cartesian' to show all test methods for all employees). SQL-

SELECT tblEmployees.EmployeeNo, Query1.TestID, Query1.Trained
FROM tblEmployees, Query1;

Thanks in advance for any help.

 

[Corticus]

Hi,

I think I got it. I made one qry to figure out who passed what:
<pre>SELECT TblEmployee.Employee, TblTraining.TestID
FROM TblEmployee LEFT JOIN TblTraining ON TblEmployee.Employee = TblTraining.Employee;</pre>

Then I made another qry that look at the first one, and lists all employees, and all tests, in all possible combinations (a 'Cartesian' join) and then matches those testID's and employeenumber to the TblTraining to see if the person passed.
The SQL for that is:
<pre>SELECT QryTraining.Employee, TestID.TestDesc, TblTraining.Trained
FROM QryTraining, TestID INNER JOIN TblTraining ON TestID.TestID = TblTraining.TestID
ORDER BY QryTraining.Employee;</pre>

Seems to do what you want...

HTH,