reduce multiple records to one

[arkres]

Hello,

I hope you don’t mind my using this forum as a learning tool. I am running an Access 2007 database. I am trying to create a query from 3 tables:
TblTrainingPrograms containing the following fields
Program Number (unique value)
Program Title
Program Date

TblInstructors with these fields
InstructorName, address, etc.

TblInstructorProgram with the fields
InstuctorID
ProgramNumber(linking this instructor to a particular training)
InstructorNumber(in the case of multiple instructors for the same training, the user may add new records and enter “1”, “2” or “3” here)

I need to run a query that will display even multiple instructor programs in one record, so that Instructor# 2 will appear in another field in the same record rather than having 2 or three records displayed in the datasheet view. I’ve tried several combinations, but when there is only one instructor, the Program does not appear in the query results.
I don’t really know a lot about SQL, but I’m hoping there might be a way to do this that way. Any help would be appreciated.

Thanks in advance,

Pat

 

[neileg]

If you have a fixed maximum number of instructors, eg 3, it's not too hard. Create three queries, one selecting each instructor number. Then create a fourth query and add the three queries joining them on the program number with a LEFT join - join the #1 query to the #2 query, showing all records from #1 and those records from #2 that match, ditto for #2 to #3.
Since you need to have a query for each instructor number this won't work unless you know what the maximum is. Also, you'll have a problem if you have a #1 and a #3 but #2 is missing.

 

[arkres]

Brilliant. Thanks so much!

Best,

Pat