Have results show on one line per customer order?

[Number11]

Hello,

So i need to have the query show one line per customer order, the fields i have are as follows


Order_Date
Order_Number
Product_ID

results i see is

Order_Date Order_Number Product_ID
25/06/2024 592572 RRW01
25/06/2024 592572 RYTA02


i would like to see then like this is this possible?

Order_Date Order_Number Product_ID_1 Product_ID_2
25/06/2024 592572 RRW01 RYTA02

 

[plog]

Maybe, but its not going to be easy.

1. Does the Product_ID number matter? RRW01 could be Product_ID_1 just as easily as it could be Product_ID_2 in the above example, right? If you do care about the number they get, please explain the precedence rules.

2. Could you have duplicate records? Could this be in the table:

Order_Date Order_Number Product_ID
25/06/2024 592572 RRW01
25/06/2024 592572 RYTA02
25/06/2024 592572 RYTA02
25/06/2024 592572 RYTA02

If so, how would you like that displayed?

 

[June7]

It's possible. Options:

1. http://allenbrowne.com/func-concat.html

2. https://stackoverflow.com/questions/64400911/pivot-query-in-ms-access

 

[Gasman]

@theDBguy also has his version.

Code Snippets

 

[Ken Sheridan]

The following uses the highly efficient GetString method of the ADO Recordset object:

Public Function GetList(strTable As String, strColumn As String, strSortColumn As String, strDelim As String, Optional strFilter As String = "True") As String

    Const NOCURRENTRECORD = 3021
    Dim rst As ADODB.Recordset
    Dim strSQL As String
    Dim strList As String
    
    strSQL = "SELECT " & strColumn & " FROM " & strTable & " WHERE " & strFilter & " ORDER BY " & strSortColumn
  
    Set rst = New ADODB.Recordset
    
    With rst
        Set .ActiveConnection = CurrentProject.Connection
        .Open _
            Source:=strSQL, _
            CursorType:=adOpenForwardOnly, _
            Options:=adCmdText
        
        On Error Resume Next
        strList = .GetString(adClipString, , strDelim, strDelim)
        .Close
        Select Case Err.Number
            Case 0
            ' no error so remove trailing delimiter
            ' and return string
            GetList = Left(strList, Len(strList) - Len(strDelim))
            Case NOCURRENTRECORD
            ' no rows in table so return
            ' zero length string
            Case Else
            ' unknown error
            GetList = "Error"
        End Select
    End With
    
End Function

 

[Ken Sheridan]

Order_Date Order_Number Product_ID_1 Product_ID_2
25/06/2024 592572 RRW01 RYTA02

An easy way to achieve such a layout is to use a report which returns distinct order date and number values, and place a subreport alongside the rightmost control in the detail section. The subreport should be in multi-column format with across-then-down column flow. If there are insufficient columns to accommodate all of the products for an order in a single line the columns will wrap to the next line, with each control being aligned with those in the row above. The image below is of a report which lists the items (and quantity) purchased by each customer: