Simple Query 2 tables - Am I missing the point completly??

[rossmcauslan]

I have a training database which I maintain. Within this there are two tables that I am having problems with. One holds information on all of the possibles training courses that staff can take and the other holds the data on which members of staff have been on these courses. I need to construct a query that will show what courses that a particular member of staff has attended but I also need to show the ones that he hasn't attended. I feel that I must be missing something obviuos but I just can't seem to get this to work.<br>
<br>
Any suggestions would be much appriciated<br>
<br>
Cheers<br>
Ross McAuslan

 

[Richard]

Im not sure how your fields are set up, but you could fill in 'Is Null' under criteria. This will give you records where a certain staff member has not taken a course (as the field is null-there are no records of this member of staff having taken the course). For those that have taken a certain course you type 'Is Not Null'.

 

[DougP]

Well first of all the tables have to relate correctly to one another.<br>
OK<br>
So Table1 has Employees and Table2 has Classes and a third table has just the EmployeeID and a ClassID. So an employee has taken 3 classes there will be three records like so<br>
EmployeeID ClassID<br>
Empl1 2345<br>
Empl1 4567<br>
Empl1 9876<br>
<br>
Then base your query on this table<br>
<br>
<br>
<br>

 

[elizabeth]

You might want to try a crosstab querybased on the table that contains the members and courses? You can use course names for Rows, the Employees as Columns (maybe you'll have to use IDs or initials if there are a lot) and use count to reflect 1 = taken, blank = not taken. Use the crosstab query wizard.