shairal said:
Hello Access Experts ~
I'm stumped! I have two tables, Parts and Orders. I have a query
that returns my data as:
PartNbr QtyOrdered OrderID
ABC123 10 id1234
ABC123 20 id1244
ABC123 30 id1233
DEF456 10 id2233
DEF456 20 id2433
I need to provide our corporate an Excel report that merges the Qty
and OrderID into one cell for each PartNbr:
Column A Column B Column C
Row 1 ABC123 10 id1234
20 id1244
30 id1233
Row 2 DEF456 10 id2233
20 id2433
Is there a way to write a query that would export to Excel like this?
I don't want to spend my time in Excel merging data together. Help
please!
This requires some VBA code: You will need to create a function to
concatenate the values. At the bottom is a function I based on one
written by Dev Ashish. Just copy and paste it into a module. Then create
a saved query called ForExport with this sql:
SELECT DISTINCT TEST.PartNbr,
fConcatFld("TEST","partnbr","qtyordered","string",[partnbr],Chr(10),"ord
erid") AS QtyOrdered,
fConcatFld("TEST","partnbr","orderid","string",[partnbr],Chr(10),"orderi
d") AS OrderID
FROM TEST;
Export the query to Excel. Open the resulting spreadsheet, highlight the
cells in the qty and orderid columns and format them to wrap text. Then
format all the columns so their vertical alignment is Top.
Voila
Function fConcatFld(stTable As String, _
stForFld As String, _
stFldToConcat As String, _
stForFldType As String, _
vForFldVal As Variant, _
sConcatChar As String, _
sOrderFlds As String) _
As String
Dim lodb As Database, lors As DAO.Recordset
Dim lovConcat As Variant, loCriteria As String
Dim loSQL As String
Const cQ = """"
On Error GoTo Err_fConcatFld
lovConcat = Null
Set lodb = CurrentDb
loSQL = "SELECT [" & stFldToConcat & "] FROM ["
loSQL = loSQL & stTable & "] WHERE "
Select Case stForFldType
Case "String":
loSQL = loSQL & "[" & stForFld & "] =" & cQ & vForFldVal &
cQ
Case "Long", "Integer", "Double": 'AutoNumber is Type Long
loSQL = loSQL & "[" & stForFld & "] = " & vForFldVal
Case Else
GoTo Err_fConcatFld
End Select
If Len(sOrderFlds) > 0 Then
loSQL = loSQL & " ORDER BY " & sOrderFlds
End If
'Debug.Print loSQL
Set lors = lodb.OpenRecordset(loSQL, dbOpenSnapshot)
'Are we sure that duplicates exist in stFldToConcat
With lors
If .RecordCount <> 0 Then
'start concatenating records
Do While Not .EOF
If Len(lovConcat) > 0 Then
lovConcat = lovConcat & sConcatChar &
lors(stFldToConcat)
Else
lovConcat = lors(stFldToConcat)
End If
.MoveNext
Loop
Else
GoTo Exit_fConcatFld
End If
End With
'That's it... you should have a concatenated string now
'Just Trim the trailing ;
fConcatFld = lovConcat 'Left(lovConcat, Len(lovConcat) - 2)
Exit_fConcatFld:
Set lors = Nothing: Set lodb = Nothing
Exit Function
Err_fConcatFld:
MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description
Resume Exit_fConcatFld
End Function
