Including every record in table b for each record table a.

[sdp_tws]

Hi, please can somebody help if possible...

I am trying to link two tables.

First table, "Employees" has two fields (for simplicity): employee_id and employee_name

Second table "Training Details" has three fields (for simplicity): employee_id, training_id, training_date.

The training details table only includes employees that are trained in certain items. Ie if one record read: "23, 15, 15/03/08" then that would indicate that employee #23 is trained in item #15 and was trained on 15/03/08. But it doesn't contain blank fields for items the training items that employees are not trained in. Ie if employee #21 was not trained in it #10, there'd be no field reading "21, 10, null" - it simply does not exist.

What I want is a query that will show, for each employee, a list of ALL training items - that is including the ones they're not trained on. But only including a training_date field in the ones they have been trained on.

I hope that made sense! If not, please ask me to elaborate.

So if anybody can help, it would be greatly appreciated

 

[pbaldy]

Presumably there is also a third table listing the training items? A query with that table and the employee table without a join will get you the Cartesian product, all employee/training combinations. You then create a query with that query and your details table, with left joins, which should produce your desired result.

 

[sdp_tws]

There is a third table.... and yes that's exactly what I'm looking for. It looks like it will give exactly what I'm looking for, but I will confirm when I've tried it.

Thankyou.