How to Concantenate multiple child records into one row

[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. I also have the ASP that pumps out the Undesired Results.

TIA

 

[Sheco]

Since this is in the ASP forum instead of the Access forum I'll assume that what you really care about is how to format the report rather than how to get a resultset from the database so that it is an exact match for your desired field layout.

Working under that assumption, and also assuming that you do an ORDER BY on the UnitID, why not loop through the recordset and only writing rows to the browser when UnitID changes.

Something like this:

Dim strLastUnitID, strEmpIDList
strLastUnitID = ""
strEmpIDList = ""
Do While Not rs.EoF
  'Is this the same Unit as the previous row?
  IF (rs("UnitID") = strLastUnitID) THEN
    'Yes, add employee to list
    strEmpIDList = strEmpIDList & " " & rs("EmployeeID")
  ELSE
    'No, write old unit row unless this is 1st row
    if (strLastUnitID <> "") then
       Response.Write rs("UnitID") & " &nbsp;&nbsp; " & strEmpIDList 
    end if
    
    'Reset employee list w/current employee 1st in new Unit
    strEmpIDList = rs("EmployeeID")
  END IF
  
  'Move current unit into strLastUnitID
  strLastUnitID = rs("UnitID") 

  'Move to next record
  rs.MoveNext

  'Catch last record here
  IF rs.EOF THEN
    Response.Write rs("UnitID") & " &nbsp;&nbsp; " & strEmpIDList 
  END IF
Loop

 

[ynott]

Thanks for helping, but I didn't get the results I expected. The code that you provided shows as:

Truck02 A3 A2 A1
Truck03 A7

Thus, it doesn't show Truck01. Also, Truck01's employees are on Truck02 and Truck02's employee is on Truck03. Truck03's employee is not seen at all.

I'm stumped on this one.

 

[Miked811]

Can you please provide the desired output from you example...

 

[johnwm]

Just follow through the logic path for the code given. A quick inspection of this section

'No, write old unit row unless this is 1st row
    if (strLastUnitID <> "") then
       Response.Write rs("UnitID") & " &nbsp;&nbsp; " & strEmpIDList
    end if

appears to take the value of the current rs("UnitID") rather than using LastUnitID.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first
'If we're supposed to work in Hex, why have we only got A fingers?'
Essex Steam UK for steam enthusiasts

 

[Sheco]

Yeah it was just something I whipped out without testing becuase I don't have access to his database

The real question here is if this TYPE of solution will work for ynott. Is it OK to use his existing query and just change the code for the presentation of the recordset so that the HTML output of the code is a page that shows the records as described ... regardless of the actual structure of records within the recordset???

If the answer is yes then it is just a matter of tweaking that code so that it produces the proper HTML. If the answer is no then tweaking the code is a waste of time.

 

[ynott]

Yes, I can change the query around a bit, but to what?

 

[johnwm]

Think you're right Sheco - but it looks from the OP's first post that the data is correct. OP's second post indicated that a little tweak to your code was needed. It should be easy enough for OP to get to his desired results from the guidance given

ynott If this doesn't give you a clear enough guide, then please read faq222-2244 to help you clarify your question.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first
'If we're supposed to work in Hex, why have we only got A fingers?'
Essex Steam UK for steam enthusiasts