Multiple values in only one row. Is it possible? 1

[ynott]

We have a vehicle report that lists what employees are in the truck at any given time. However, each employee's ID number lists in on one row for the same vehicle. Is there a way to get the values multiple employees within a vehicle on just one row? Here's some info so you can get a picture.

Vehicle Table
UnitID ScheduleID
Truck01 477
Truck02 481
Truck03 483

Personnel Table
ID EmployeeID Name
1 A1 Jack
2 A2 Jeff
3 A3 Jerry
4 A7 Dan
5 A9 Abe

PersonnelSchedule
scheduleID EmployeeRecordID
477 1
477 2
477 3
481 4
483 5

SELECT Vehicle.UnitID, Personnel.EmployeeID
FROM (Vehicle LEFT JOIN PersonnelSchedule ON Vehicle.ScheduleID = PersonnelSchedule.ScheduleID) LEFT JOIN Personnel ON PersonnelSchedule.EmployeeRecordID = Personnel.ID;


Undesired Results (Current Report)
UnitID EmployeeID About 7 other fields
Truck01 A1 '''''''
Truck01 A2 '''''''
Truck01 A3 '''''''
Truck02 A7 '''''''
Truck03 A9 '''''''

Desired Results (Current Report)
UnitID EmployeeID About 7 other fields
Truck01 A1 A2 A3 '''''''
Truck02 A7 '''''''
Truck03 A9 '''''''

This report really involves about 7 tables, but I tried to simplify it into just 3 tables. I have these test tables and query in an Access 2k DB if needed. TIA

 

[Crowley16]

use a crosstab query...

--------------------
Procrastinate Now!

 

[PHV]

Take a look here:

http://www.tek-tips.com/faqs.cfm?fid=4233

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244