Combining Rows - Sort Order

[theberst]

I've implemented this solution and found that it only works if the records with the same "CompanyName" are beside each other in the table. More specifically, the VBA code processes each record based on the order of primary key in the table. Is there a way to accomplish the goal of the original poster if records aren't adjacent in the table?

Thank you for your help!

[NeoPa]

The solution can be modified easily to include an ORDER BY clause in the SQL. In all my testing I never found it was necessary as the GROUP BY clause implied the same. If you are finding this is a problem though, that is certainly the thing to do.

[theberst]

Thank you for responding; I wasn't sure if anyone would be monitoring this thread for new posts. You're really quite knowledgeable after reading this thread and the one linked to in the solution at the top.

I'm running SQL along these lines:

SELECT [GroupBy1],
       [GroupBy2],
       ...,
       [GroupByN],
       Max(Concat([GroupBy1] &
                  [GroupBy2] &
                  ... &
                  [GroupByN], Item)) AS Items
FROM [YourTable]
GROUP BY [GroupBy1],
         [GroupBy2],
         ...,
         [GroupByN]
ORDER BY [GroupBy2]

I've set the VBA function to output its variables right before the "Concat = strItems" line at the end of the function. Even with the Order By, the function is processing records in the order they were put into the database table, either based on the primary key or the default ordering of the table. I've even tried creating an Advanced Sort on the table (a sort that persists even if the database is closed) so that the right records are adjacent, and the records are still processed by the function in the primary key / default order.

I must be missing something if this is working for others who have tables that weren't populated by inputting all of the records in order by "CompanyName".

e.g. my table would look like:

CompanyName        Grower    Category    Product
B Orchard            -1    Fruits        Apples
B Orchard            -1    Vegetables    Beans
B Orchard            -1    Vegetables    Other
B Star Acres        -1    Vegetables    Beets
B Farms            -1    Fruits        Blackberries
B Orchard & Roadside Market -1    Fruits        Apples
B Orchard            -1    Fruits        Blackberries
B Orchard & Roadside Market -1    Fruits        Blackberries
B Orchard & Roadside Market -1    Vegetables    Beans
B Orchard & Roadside Market -1    Vegetables    Beets
B Star Acres        -1    Vegetables    Asparagus
B Farms            -1    Vegetables    Broccoli
B Farms            -1    Vegetables    Cabbage

[NeoPa]

This thread was a post added to Combining Multiple Rows of one Field into One Result and refers to that thread.

[NeoPa]

I'd like to help if I can, but your actual SQL & VBA code would be more helpful to understand the issue than some general references to amendments made to the originals. I don't have enough to even understand the issue here I'm afraid. Something is unusual about your setup, but as I don't have that available. I depend on your sharing the information with me.

I can say that the ORDER BY clause should match the GROUP BY clause exactly. If your post is accurate (very important in situations such as these) then it could not be expected to work.

[theberst]

I'm sorry. I will include that information here:

Here is my SQL, with the ORDER BY now matching the GROUP BY:

SELECT [qry_row_aggregates-prework].bigNumber, [qry_row_aggregates-prework].Building, [qry_row_aggregates-prework].WTU, [qry_row_aggregates-numdays].NumDays, Max(Concat([qry_row_aggregates-prework].bigNumber & [qry_row_aggregates-prework].Building & [qry_row_aggregates-prework].WTU & [qry_row_aggregates-numdays].NumDays,[BusinessType])) AS Business_type
FROM [qry_row_aggregates-prework], [qry_row_aggregates-numdays]
WHERE ((([qry_row_aggregates-prework].bigNumber)=[qry_row_aggregates-numdays].bigNumber))
GROUP BY [qry_row_aggregates-prework].bigNumber, [qry_row_aggregates-prework].Building, [qry_row_aggregates-prework].WTU, [qry_row_aggregates-numdays].NumDays
ORDER BY [qry_row_aggregates-prework].bigNumber, [qry_row_aggregates-prework].Building, [qry_row_aggregates-prework].WTU, [qry_row_aggregates-numdays].NumDays;

In this SQL, I have this Concat VBA function called:

Public Function Concat(strGroup As String, _
                       strItem As String) As String
    Static strLastGroup As String
    Static strItems As String
 
    If strGroup = strLastGroup Then
        strItems = strItems & ", " & strItem
    Else
        strLastGroup = strGroup
        strItems = strItem
    End If
    MsgBox "strGroup=" & strGroup & " strLastGroup=" & strLastGroup & " strItem=" & strItem & " strItems=" & strItems
    Concat = strItems
End Function

Herein lies the issue: when MsgBox is invoked, it prints out the variables, which indicates that the records are being processed not by the ORDER BY clause order but in the original order of primary key / default order from the table. Does that make sense? In other words, based on the data in the variables, I'm able to verify the order that the Concat function is processing records.

Note that the table isn't referenced in the SQL above, because there are a few queries between this query and the table. These intermediary queries also have ORDER BY clauses that put the records in the right order. Alas, it seems that the VBA code is not paying attention to that order.

[NeoPa]

May I ask why you are working with a Cartesian Product of the two queries in the FROM clause?

Put another way, why is there no JOIN in your FROM clause?

[NeoPa]

Note that the table isn't referenced in the SQL above, because there are a few queries between this query and the table. These intermediary queries also have ORDER BY clauses that put the records in the right order. Alas, it seems that the VBA code is not paying attention to that order.

By the way, the VBA code is invoked in the order that the SQL processes the data. The ordering is only relevant in the SQL, though your VBA trick of displaying the data does give us a reliable window on how (in which order) the data is being processed.

[theberst]

@NeoPa
Thanks for your suggestion. I've updated the SQL to include the JOIN:

SELECT [qry_row_aggregates-prework].bigNumber, [qry_row_aggregates-prework].Building, [qry_row_aggregates-prework].WTU, [qry_row_aggregates-numdays].NumDays, Max(Concat([qry_row_aggregates-prework].bigNumber & [qry_row_aggregates-prework].Building & [qry_row_aggregates-prework].WTU & [qry_row_aggregates-numdays].NumDays,[BusinessType])) AS Business_type
FROM [qry_row_aggregates-prework]
INNER JOIN [qry_row_aggregates-numdays]
ON [qry_row_aggregates-prework].bigNumber=[qry_row_aggregates-numdays].bigNumber
GROUP BY [qry_row_aggregates-prework].bigNumber, [qry_row_aggregates-prework].Building, [qry_row_aggregates-prework].WTU, [qry_row_aggregates-numdays].NumDays
ORDER BY [qry_row_aggregates-prework].bigNumber, [qry_row_aggregates-prework].Building, [qry_row_aggregates-prework].WTU, [qry_row_aggregates-numdays].NumDays;

As far as I can tell, the behavior is the same. What else should I try?

[NeoPa]

That's interesting :S

I cannot see why you would get the results you describe. It might be interesting to look at the database itself if that is ok. I've copied below a general set of instructions for attaching databases. If you want to I suggest you attach yours for a closer look.

When attaching your work please follow the following steps first :

1. Remove anything not relevant to the problem. This is not necessary in all circumstances but some databases can be very bulky and some things do not effect the actual problem at all.
2. Likewise, not entirely necessary in all cases, but consider saving your database in a version not later than 2003 as many of our experts don't use Access 2007. Largely they don't want to, but some also don't have access to it. Personally I will wait until I'm forced to before using it.
3. If the process depends on any linked tables then make local copies in your database to replace the linked tables.
4. If you've done anything in steps 1 to 3 then make sure that the problem you're experiencing is still evident in the updated version.
5. Compile the database (From the Visual Basic Editor select Debug / Compile {Project Name}).
6. Compact the database.
7. Compress the database into a ZIP file.
8. When posting, scroll down the page and select Manage Attachments (Pressing on that leads you to a page where you can add or remove your attachments. It also lists the maximum file sizes for each of the allowed file types.) and add this new ZIP file.

It's also a good idea to include some instructions that enable us to find the issue you'd like help with. Maybe some instructions of what to select, click on, enter etc that ensures we'll see what you see and have the same problems.

[theberst]

@NeoPa
I've been doing some debugging and found the following. Please follow this logic:

The Max(Concat()) function is in field X of Query B. Query B queries Query A. Query B does not receive concatenated data in field X in all instances where it should.

If the results of Query A are in a table, Table A (via make-table query), Query B queries Table A and receives concatenated data in field X in all instances where it should.

Since Query A and Table A contain the same data, why do Query B's results differ?

[NeoPa]

Access (& most DBMSs) see the sort order of a query as something important for displaying, but may override it for subqueries if it determines that performance is enhanced by different sorting. Tables have a built-in sort order that a query will use by default.

I suspect if this is noticed that you have some sort of problem along these lines. Without seeing the database though, I'm just using my licked finger.

BTW. It's a good thing that you're doing the debugging first. That sort of approach will certainly help you.