access performance, mysql nested functions

Question

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.

I guess my best bet will be using native mysql functions and execute my sql in mysql. My access function concatenates and extracts a total sum thats allready there (Tqty) , to give this kind of result:

name , class , Tqty , QTY
joe , A , 20 , 10
joe , A , 20 , 9

joe , A , 20 , 1

joe , B , 30 , 30

name , classWTqty
joe , a (20) b(30)

(note: In access I made column Tqty because I was having trouble summing QTY in my original dconcat access function)

I 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. Whats do you suggest?

Answer 1

I'm not sure why you need Dconcat, you should be able to do what you want with straight queries.