Multiple values in one row. Possible? 2

[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
Truck01
Truck02
Truck03

Personnel Table
EmployeeID Name
A1 Jack
A2 Jeff
A3 Jerry
A7 Dan
A9 Abe

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

Desired Results
Unit Employee About 7 other fields
Truck01 A1 A2 A3 '''''''
Truck02 A7 '''''''
Truck03 A9 '''''''

Notice how Truck01 has all three employees.

TIA

 

[dgillz]

How are these tables related? I see no common field.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[ynott]

You're right. I tried to oversimply things since there are really 7 tables involved. Here's another crack at it with just 3 tables.

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 '''''''

I might have oversimplied it still, but I think this will do. Thanks

 

[lbass]

You could use the three-formula method to collect the IDs. First insert a group on {Vehicle.UnitID} and then create these formulas:

//{@reset} to be placed in the group header:
whileprintingrecords;
stringvar id := "";

//{@accum} to be placed in the detail section:
whileprintingrecords;
stringvar id := id + {Personnel.EmployeeID} + " ";

//{@display} to be placed in the group footer:
whileprintingrecords;
stringvar id;

If other fields are the same regardless of truckID, you can drag those fields into the group footer. Otherwise, you would have to use some kind of summary. You would then suppress the details section.

-LB