ConcatRelated Syntax Error

[theDBguy]

DBGuy, that works. Thank you for the solution!

However it seems odd that you would have to make a select distinct query and then pull the data from that. I can't see a reason why you can't just pull from the table directly. I guess it doesn't matter as long as it works, just irritates me I can't find a simpler solution.

Thank you!!

Hi. You're welcome. Yes, it would work just fine using only one query, as Minty posted earlier. However, when I realized you're trying to combine all the Regions from the same table, I thought that is probably why it's running slow for you (you said 3 minutes earlier). The reason for it is because Allen Browne's function is being executed for each row of the query, which means if you had 100 records but only 10 distinct records, the function is still called 100x100 times. The immediate result would be 100 records with a bunch of duplicates, which the DISTINCT keyword then pares down to only 10. Whereas, using a separate query to perform the paring down to 10 distinct records before running Allen Browne's function, then we only get it run 10x100. Normally, you would have a separate table for Regions, which contains unique records, and then use it against your table when using Allen Browne's function. Hope it makes sense...

 

[kvar]

This is just a report I received in Excel and I just need to combine the records to make the report look better. It's a one time thing so that's why there aren't separate tables or a whole database.
Using the query Minty posted gives me nothing in regions, just all blank.
I did discover taking out the "DISTINCT" makes it run as quickly as it should. And the distinct shouldn't be needed anyway because it's already concatenating. But why nothing shows up in regions is beyond me.

 

[kvar]

No idea what I changed, but it's perfect now!

Thanks again everyone!

SELECT CopyOfSkus.MDTRegionalCFN, ConcatRelated("[Region]","CopyofSkus","MDTRegionalCFN= '" & [MDTRegionalCFN] & "'") AS Regions
FROM CopyOfSkus;

 

[theDBguy]

Hey. Good luck with your project.

 

[Gasman]

Would that be because you have nothing related.?
That function expects to find records in a linked table that matches a field, not the same table. Those records are not related?

Edit:
Just tested it on a table of mine, I it still works.

? concatrelated("description","Transactions","Description = 'Mr A.Selby:205318'")
Mr A.Selby:205318, Mr A.Selby:205318, Mr A.Selby:205318, Mr A.Selby:205318, Mr A.Selby:205318, Mr A.Selby:205318, Mr A.Selby:205318

Using the query Minty posted gives me nothing in regions, just all blank.
But why nothing shows up in regions is beyond me.