Report based on 2 tables - ignoring link

[amberlynn]

I'm not sure why I'm having so much trouble with this...it seems as though it should be simple.

My database has 2 tables, Training & Employees.
The Training table contains multiple records, linked to each employee.
I'm trying to create a report based on each Training Item, listing every employee (whether or not they have an associated record in the Training table).

So for example, if I choose 'Employee Orientation' as my training item, I want my report to show this as a header, then list EVERY single employee.
Beside each employee is a trainedDate field that will be blank if they haven't taken that training.

Can someone help me out on how to set this up??

Thanks!!
Amber

 

[PHV]

Have a look at LEFT JOIN.

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

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

 

[amberlynn]

Thanks PHV,
But I still don't see how to get the results I want.
Maybe I can't do it..

I'll try to explain better...
If I have 3 types of training in table1 (Safety, Driving & WCB) and 5 employees in table 2 (Emp1, Emp2, ...)
What I want is to run a report where I select a Safety 'type' (eg 'Driving') and see all 5 employees listed, and if they have taken the safety driving (if not ,then a null - but the employee still shows up).

Is that possible??

Thanks!,
Amber

 

[mrf1xa]

Still think this is caused by the link between your tables as PHV says.

I am assuming your report is based on a query, and that the query contains both tables. I further assume the training table has an employeeID or similar filed that refers to the employee table?

In the design view of the query, ensure these two fields are linked (they will be anyway of you have set up your table relationships correctly, but if not link them in the query grid). Now right click on the join and under Join Properties select the option to 'show all records from employees and only those records from training where the joined fields are equal'. This should give what you want.

Nigel
Didn't someone say work is supposed to be fun? They didn't have computers then I guess....

 

[PHV]

In fact you should have 3 tables as you have a many to many relationship:
tblEmployees
tblTrainingItems
tblTraining

Have a look here:

http://r937.com/relational.html

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