Add multiple from one table to a single record on another table

[LDD]

I have two tables

tbl_Orders
Cust OrderNo OrdLne Prod Qty
ABC SO123 1 AA 15
ABB SO124 1 AB 10

tbl_StockLoc
Prod Loc Qty
AA ZX12 10
AA ZZ12 10
AA ZZ13 50
AB CW12 15

One product can have up to 10 locations in the tbl_StockLoc

I need to print the following report (up to LOC10)
Cust OrderNo OrdLne Prod Qty Loc1 Qty1 Loc2 Qty2 Loc3 Qty3 ....
ABC SO123 1 AA 15 ZX12 10 ZZ12 10 ZZ13 50
ABB SO124 1 AB 10 CW12 15

 

[billmeye]

You could concatenate each row into a single string as long as you don't actually need to have each location in it's own column.

Function Concat(Prod As String) As String
Dim Rst As DAO.Recordset, MySql As String
Concat = ""
MySql = "SELECT * FROM tbl_StockLoc WHERE ((([Prod])='" & Prod & "'));"
Set Rst = CurrentDb.OpenRecordset(MySql, dbOpenSnapshot)
If Rst.RecordCount > 0 Then
Rst.MoveLast
Rst.MoveFirst
Do While Not Rst.EOF
    If Nz(Concat, "") = "" Then
    Concat = Rst!Loc & " " & Rst!Qty
    Else
    Concatenate = " -  " & Concat & ", " & Rst!Loc & " " & Rst!Qty
    End If
Rst.MoveNext
Loop
End If
Rst.Close
Set Rst = Nothing
End Function