Access to mysql concat sum
Posted on 2010-09-08
I have a table in access with about 180k records. I have a bunch of sql statements that I tested and work perfectly on a smaller version of my data. With access & that amount of records, things are pretty slow, but a sql statement with a Dconcat function I even let operate overnight didn't get even close to 1/4 way through.
I assumed the Jet engine wont cut it for all those records, I transfered my data to mysql and added a primary key. In access I then "linked" the table to mysql via ODBC and still gives signs of taking forever and leaving it for a half hour.
As a last attempt I'm going to try native mysql functions and execute sql in mysql. My access function concatenates and extracts a total sum thats allready in the table to give this kind of result:
name class Tqty QTY name classWqty
joe A 20 10 joe a (20), b(30)
joe A 20 9
joe A 20 1
joe B 30 30
In access I made column Tqty because I was having trouble summing QTY in the original dconcat access function.
I'm new to mysql but found theres a concat & group_concat function in mysql that might do the trick, otherwise I imagine I'll have to do a user defined function unless theres a way to nest, them of which I don't know a thing about. Do you have anything that will work?