SQL Query to give recordset

[gazza11]

I suppose I should be thankfull - 12 months ago I had great difficult creating SQL statements/queries and today I can at least perform basic queries etc. That said I spent hours last night trying to create a query that performs the following.

I have three Tables.

Employees with Fields EmpID, LastName and FirstName
Subjects - with fields NumID, SubID, EmpID and DateCompleted
SubDescr - with fields SubID and SubjectDescription

The table employees contains a list of peoples names. SubDescr contains a list of Subject Descriptions (ie Maths, English, Science and Drawing). The table Subjects contains a list of all people (EmpID) who have completed the subjects (SubID) and the date they completed the subject.

What I am trying to do is perform a query that provides a recordset of employees and details on whether they have completed the subjects or not - if they have what date was it completed. I seem to be able to get a recordset with names listing subjects completed but not on the same line. For example say Fred Smith has completed Maths and Drawing and Bill Jones has completed only Maths, can I get a query result as follows:

Fred Smith Maths 2/2/2003 Drawing 3/3/2003
Bill Jones Maths 5/5/2003

 

[dodge20]

I don't think you can using a query the way your tables are currently set up. You are probably getting results like

Fred Smith Maths 2/2/2003
Fred Smith Drawing 3/3/2003
Bill Jones Maths 5/5/2003

Unless you change the structure of your subjects table, I don't think you can do it.

Dodge20