Multiple Line Data

[jfgambit]

Anyone have any suggeestions on how to convert the following query output:

VclID Option
1 Power Steering
1 Power Windows
1 Cruise Control

to

VclID Option
1 Power Steering, Power Windows, Cruise Control

I need to export the file out as a csv file for uploading to a webpage and I need it in the above format so that it exports as:

"VCLID", "OPTION"
"1", "Power Steering, Power Windows, Cruise Control"

Thanks....

 

[dcx693]

You will need some progamming whichever way you proceed. You can create a query with unique VclID numbers, then go through your data as a recordset, looking for matching Options and concatenating them.

Else you can try using the ideas from this thread, especially the link to the Knowledge Base article posted by Pat Hartman. It relates to reports, but I believe you can output reports as csv.

 

[jfgambit]

Thanks...unfortunately the MS Knowledge base is down...

 

[jfgambit]

Actually, i found the following code...

Works nicely:

Public Function fConcatenateRecords(strField As String, strRecordset As String, strFieldSeparator As String) As String

'USAGE:
'fContatenateRecords(FieldName to concatenate, Table; Query; or SQL SELECT recordset, Separator character)
'NOTE:
'DAO recordset is being used to provide backward compatability with Access 97
'Make sure you enable the DAO reference On Error Resume Next
'To prevent query from hanging no error trapping involved
'If no records are return, you should look for the problem with your SQL SELECT statement

Dim curDB As DAO.Database
Dim rst As DAO.Recordset
Dim strTemp As String

Set curDB = CurrentDb
Set rst = curDB.OpenRecordset(strRecordset)

With rst
If .EOF And .BOF Then
fConcatenateRecords = "" 'no records returned
Exit Function
End If

.MoveFirst
While Not .EOF
strTemp = strTemp & .Fields(strField) & strFieldSeparator & " "
.MoveNext
Wend
.Close
End With

strTemp = Left(strTemp, Len(strTemp) - (Len(strFieldSeparator) + 1))
fConcatenateRecords = strTemp
End Function

 

[dcx693]

Looks good. The recordset you send to it needs to be for your desired VclID number, right? since it doesn't differentiate between different ones.

 

[jfgambit]

Yea...that's the problem I am trying to figure out now...

I'm getting the following

VCLID Options
1 Power Windows, Power Doors, Cruise Control, Power Steering
2 Power Windows, Power Doors, Cruise Control, Power Steering

instead of
VCLID Options
1 Power Windows, Cruise Control
2 Power Doors, Power Steering

 

[dcx693]

I think you need to specify a recordset with just the particular VclID when calling this function. Like "SELECT * FROM tbl WHERE VclID=1;", then calling it again with VclID=2, etc...

Put it all into a loop and write the result to a table.

 

[jfgambit]

Actually, I could pass the SQL SELECT statement to the function instead of the query name and add the criteria to return only those records associated with the current vehicle ID.

SELECT tblVehicleOption.VehicleID, fConcatenateRecords("[Option]"," SELECT tblVehicleOption.VehicleID, tblOption.OptionID, tblOption.Option
FROM tblOption INNER JOIN tblVehicleOption ON tblOption.OptionID = tblVehicleOption.Option WHERE tblVehicleOption.VehicleID = " & [VehicleID] & ";",",") AS Expr1
FROM tblVehicleOption;