Solved

Access to mysql concat sum

Posted on 2010-09-08
19
853 Views
Last Modified: 2013-11-27
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?
0
Comment
Question by:slothnet
  • 5
  • 4
  • 3
  • +4
19 Comments
 
LVL 51

Expert Comment

by:HainKurt
ID: 33628877
please post a sample data and what are you trying to get from this sample data...
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 33628977
also it is not a good idea to add a new column to keep the sums... you should be able to get that data with query...

select [name], sum([qty]) as sum_qty
where [name]='joe'

or you can group by to get all

select [name], sum([qty]) as sum_qty
group by [name]
order by [name]

0
 
LVL 2

Expert Comment

by:regality
ID: 33629393
It looks like you need to normalize your database. putting everything into one big table is usually a really bad idea, and is probably contributing to your speed problem. The wikipedia article on it is probably sufficient for what you are doing right now: http://en.wikipedia.org/wiki/First_normal_form

Furthermore, database engines weren't designed with creating strings of that size. group_concat for example only does 1024 characters. You will probably want to stream it out to a file as you calculate it, or to the web if that is where you are looking at the data. Are you using php with this? or a different language? Where do you want the output of this to end up?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33629590
Also email the author of the dconcat function and ask if there are any performance issues with large datasets
http://www.experts-exchange.com/M_2428436.html

You can also try a Crosstab query to get the output you are looking for:

TRANSFORM First(YourTable.[class]) AS FirstOfclass
SELECT YourTable.[name]
FROM YourTable
GROUP BY YourTable.[name]
PIVOT YourTable.[Tqty];


;-)

JeffCoachman
0
 

Author Comment

by:slothnet
ID: 33630770
@hainkurt     sample data is above to the left, what I want, to the right
@regality
For each record I doubt I'll need more then 150 characters.  The access version of the file is nearly 300megs.  So your saying if I normalize my data I shouldn't have a problem.   Have you actually been in a similar situation where doing that worked?
I've normalized data before, do you believe setting table relationships relevant just to get the concatenation out of the way?
@boag2000
I'll ask.  Crosstab query stopped at 306 headers.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 33631696
try this

select [name], 'a(' & nz(sum_qty_A,0)  & '), b(' & nz(sum_qty_B,0) & ')'  as classWqty
from
(select [name], sum([qty]) as sum_qty_A where class='A' group by [name]) a
left join
(select [name], sum([qty]) as sum_qty_B where class='B' group by [name]) b on a.[name]=b.[name]

how many class do you have?
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 33632331
slothnet,

I presume you meant the Access UDF DConcat as found in my article http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2380-Domain-Aggregate-for-Concatenating-Values-by-Group-in-Microsoft-Access.html

Based on your sample above, this should work:

Function DConcat(ConcatColumns As String, Tbl As String, Optional Criteria As String = "", _
    Optional Delimiter1 As String = ", ", Optional Delimiter2 As String = ", ", _
    Optional Distinct As Boolean = True, Optional Sort As String = "Asc", _
    Optional Limit As Long = 0)

SELECT [name], DConcat("[class], '(' & [Tqty] & ')'", "[tablename]", "[name] = '" & [name] & "'", ", ", " ") AS Concat
FROM [tablename]
GROUP BY [name]


What SQL statement did you try?  Also, I greatly recommend putting an index on that [name] column.  That all by itself should speed things up significantly.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 33632925
you can use crosstab query

save this query as q_classes

then you can use

SELECT name, "A(" & [A] & "), B(" & [B] & "), C(" & C & "), D(" & D & ")" as qty
FROM q_classes;

to get

name      qty
joe      A(20), B(13), C(5), D(7)
name	class	qty

joe	A	10

joe	A	9

joe	A	1

joe	B	3

joe	B	10

joe	C	5

joe	D	7



name	Total Of qty	A	B	C	D

joe	45	20	13	5	7

Open in new window

0
 
LVL 51

Expert Comment

by:HainKurt
ID: 33632933
oops, i forgot to post the query ;)

save this as q_classes and use the query posted above for final solution
TRANSFORM Sum(classes.qty) AS SumOfqty

SELECT classes.name, Sum(classes.qty) AS [Total Of qty]

FROM classes

GROUP BY classes.name

PIVOT classes.class;

Open in new window

0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 8

Expert Comment

by:kingjely
ID: 33633396

Hi Slothnet. Cool name ;)

To concat the fields in mysql, as you have in your example would be;

Select name, class,  Tqty,  QTY,  name,  concat( substring(class,1), '(' , (substring(Tqty,1), ')' ) as  classWqty from yourtable
group by class, name
order by class, name;

That doesn't put classWqty output side by side.. but if you combine some of the above answers with this Mysql Concat query, maybe you could get the disired output.

Kind regards,
Kj
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33644806
It may just be me, but I am a firm believer that mySQL will eat MS Access for breakfast AND lunch.
Give this a try.
create table tbl (name varchar(10), class char(1), Tqty int, QTY int);

insert into tbl values ('joe', 'A', 20, 10);

insert into tbl values ('joe', 'A', 20, 9);

insert into tbl values ('joe', 'A', 20, 1);

insert into tbl values ('joe', 'B', 30, 30);

insert into tbl values ('mary', 'B', 30, 30);



select name, group_concat(x) classWQty

from

(

	select name, group_concat(concat(class,'(',Tqty,')')) x

	from

		(select distinct name,class,Tqty from tbl) t1

	group by name,class

) t2

group by name;

Open in new window

0
 
LVL 8

Expert Comment

by:kingjely
ID: 33644836

For what its worth, my query would have errored, 1 to many brackets.. but I'm sure Cyber has it again ;)

Select name, class,  Tqty,  QTY,  name,  concat( substring(class,1), '(' , substring(Tqty,1), ')' ) as  classWqty from yourtable
group by class, name
order by class, name;
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33645553
ck,

>>It may just be me, but I am a firm believer that mySQL will eat MS Access for breakfast AND lunch.

No, it's not just you :)

<soapbox>

But then again, that's like saying, "It may just be me, but I am a firm believer that a Lexus will eat a Toyota Corolla for breakfast AND lunch."  Both are sedans, just as MySQL and Access are both databases, but both are very clearly in different classes of products, with different target audiences.

Both are legitimate products, and both have advantages as well as disadvantages.  There are specific applications for which Access is better suited, and there are specific applications for which MySQL is better suited.

</soapbox>

BTW, a little database humor.  Be forewarned that the video has some salty language, so if you are easily offended by that, or if such things are frowned upon at your workplace, exercise your discretion:

http://www.youtube.com/watch?v=b2F-DItXtZs
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33645667
<ot>
@Patrick

Ah.. I see how it is being read.
>>It may just be me, but I am a firm believer that mySQL will eat MS Access for breakfast AND lunch.

I should have added a qualifier/disclaimer that this statement is expressly for the question/task at hand, that is, purely from a performance viewpoint against a sizeable database.  I have nothing against Access.
</ot>
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33645690
No worries, ck.  Blame it on the morning coffee not being 100% effective yet :)
0
 

Author Comment

by:slothnet
ID: 33654102
I'm not going to comment on what doesn't remotely seem is relevant to my question.

I didn't yet get a chance to try the other solutions.

@matthewspatrick
Indexes made it work.  I originally sent in Tqty a different way by aggregating where string value gets set.  I went through quite a bit of formal discovery to make it work, you achieve the same result without altering anything, nice

@cyberkiwi
Looking forward to try out the mysql statements though I'm done.

0
 
LVL 8

Expert Comment

by:kingjely
ID: 33655580
Haha awww told ;)

@Sloth
I didn't even get a reply to a comment that may help you understand mysql Concat, Have a go..

Select name, class,  Tqty,  QTY,  name,  concat( substring(class,1), '(' , substring(Tqty,1), ')' ) as  classWqty from yourtable
group by class, name;

Good times ;)
Kj
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36157620
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Creating and Managing Databases with phpMyAdmin in cPanel.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now