Concatenating related fields

[btappan]

I have data like this:



Table ExistingBinData:
VariantName|Bin
XYZ|QTY. 5 @ SHELF 3
XYZ|QTY. 1 @ SHELF 2
XYZ|QTY. 1 @ SHELF 17
ABC|QTY. 3 @ SHELF 13
ABC|QTY. 2 @ SHELF 12

I used Allen Browne's Concat Related function found here: http://allenbrowne.com/func-concat.html to get the below result:

SELECT ExistingBinData.VariantName, ConcatRelated("Bin","ExistingBinData","[VariantName] = """ & [VariantName] & """") AS Bins
FROM ExistingBinData
ORDER BY ExistingBinData.VariantName;

Query ExistingBinsUngrouped:
VariantName|Bins
XYZ|QTY. 5 @ SHELF 3, QTY. 5 @ SHELF 3, QTY. 1 @ SHELF 17
XYZ|QTY. 5 @ SHELF 3, QTY. 5 @ SHELF 3, QTY. 1 @ SHELF 17
XYZ|QTY. 5 @ SHELF 3, QTY. 5 @ SHELF 3, QTY. 1 @ SHELF 17
ABC|QTY. 3 @ SHELF 13, QTY. 2 @ SHELF 12
ABC|QTY. 3 @ SHELF 13, QTY. 2 @ SHELF 12

The above query takes just a couple seconds to run. My problem is that I need to Group By VariantName but the moment I do so, whether adding Group By within the query above, or by creating another query to query the query above and using Group By there and either way makes the query take about 15 minutes to run to Group 15k records down to 10k. Any Ideas on how to speed it up?

[ranman256]

=field1 & field2

[btappan]

Sorry, but can you elaborate on that at all? Just to be clear, I am not concatenating fields, I am concatenating records into one string.

[Micron]

what you posted looks grouped (XYZ, ABC). If you simply didn't show how it's not grouped, then maybe what you simply need is to sort on VariantName.

[btappan]

If it was grouped on VariantName, wouldn't ABC, XYZ not be repeated? What I have posted above is not grouped.

[Micron]

No, it would still repeat if we're talking about a query. Otherwise, what would you expect to see in that field on the 2nd and 3rd records?

What I have posted above is not grouped.

Maybe it's not specifically grouped using the Group clause in a query, but the way you show it is represented as such. Thus it either has to be grouped, or it's sorted, or it's just coincidence that it looks like it could be either.

[btappan]

Well if I add "GROUP BY ExistingBinData.VariantName"

SELECT ExistingBinData.VariantName, ConcatRelated("Bin","ExistingBinData","[VariantName] = """ & [VariantName] & """") AS Bins
FROM ExistingBinDataa
GROUP BY ExistingBinData.VariantName
ORDER BY ExistingBinData.VariantName;

I get the below, without repeating rows for VariantName , which is the desired outcome, it just takes a very long time, which is the reason for my post.

XYZ|QTY. 5 @ SHELF 3, QTY. 5 @ SHELF 3, QTY. 1 @ SHELF 17
ABC|QTY. 3 @ SHELF 13, QTY. 2 @ SHELF 12

[Micron]

Sorry, with all that concatenation I missed that each record for a VariantName was exactly the same. Did you try the DISTINCT (or DISTINCT ROW) predicate and see how long that takes? I would suspect less time than GROUP BY, but don't know by what factor.

[June7]

Whether DISTINCT or GROUP BY, functions like ConcatRelated can cause slow performance in queries with large datasets. Possibly only way to speed up is to save the un-grouped recordset to a 'temp' table (table is permanent, records are temporary) then run GROUP BY or DISTINCT on that table.

[btappan]

Thanks for the suggestion, I tried DISTINCT ROW ....didn't change the outcome at all, still had repeated rows, DISTINCT works, but takes even longer than the Group By option.

[btappan]

I wound up turning the ungrouped query into a make table query, and then ran a grouped query on the make table instead. That dropped the execution time down from 15 minutes, to around 1 minute. Thanks for the suggestions guys.