combining query?

[rzkl]

Hello,
Not sure what I would search for on this but I have a table that has two columns, one is an item number and one is a qty. What I am trying to get is a query that will combine all the records together in one long string with a comma separating each number. see example


Thanks in advance

Table

SKU Qty
12345 1000
56789 2000
23456 3000

desired Query result

12345,1000,56789,2000,23456,3000

 

[bob fitz]

I don't know how to do this with a query, but until someone else can help with that, you may want to try using a user defined function. Something like:

Public Function fnMyFunction() As String
On Error GoTo Err_fnMyFunction_Error
  Dim sResult As String
  Dim rec As DAO.Recordset
 
  Set rec = CurrentDb.OpenRecordset("[COLOR=red][B]YourTableName[/B][/COLOR]")
  With rec
    Do Until .EOF
      sResult = sResult & .Fields("SKU") & ", " & .Fields("Qty") & ", "
      .MoveNext
    Loop
  End With
 
  If Len(sResult) > 0 Then
    sResult = Left(sResult, Len(sResult) - 2)
  End If
 
  fnMyFunction = sResult
Exit_ErrorHandler:
  rec.Close
  Set rec = Nothing
  Exit Function
Err_fnMyFunction_Error:
  MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure fnMyFunction at Line " & Erl
  Resume Exit_ErrorHandler
End Function

You would just need to change YourTableName

 

[rzkl]

Thank you for your response. The reason I was trying to accomplish in a query is that i do not have any experience in VBA, still trying to learn. If there is anymore direction that you can give me on this it would be great. Not sure where to put this code or how to retrieve my answer.


Thank you

 

[humph]

Hi

Why not try in a new field in design mode on your query open the builder

Then select [field 1]&", "& [field2]


This would give a field on your query
12345, 1000
56789 2000
23456 3000

You can then do a cross tab query on the result to get one long line

You will need to play around with the cross tab to get exactly what you want but is doable in a two stage query process.