• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 878
  • Last Modified:

Access to mysql concat sum

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
slothnet
Asked:
slothnet
  • 5
  • 4
  • 3
  • +4
1 Solution
 
HainKurtSr. System AnalystCommented:
please post a sample data and what are you trying to get from this sample data...
0
 
HainKurtSr. System AnalystCommented:
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
 
regalityCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Jeffrey CoachmanCommented:
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
 
slothnetAuthor Commented:
@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
 
HainKurtSr. System AnalystCommented:
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
 
Patrick MatthewsCommented:
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
 
HainKurtSr. System AnalystCommented:
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
 
HainKurtSr. System AnalystCommented:
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
 
kingjelyCommented:

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
 
cyberkiwiCommented:
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
 
kingjelyCommented:

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
 
Patrick MatthewsCommented:
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
 
cyberkiwiCommented:
<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
 
Patrick MatthewsCommented:
No worries, ck.  Blame it on the morning coffee not being 100% effective yet :)
0
 
slothnetAuthor Commented:
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
 
kingjelyCommented:
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
 
Patrick MatthewsCommented:
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

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 5
  • 4
  • 3
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now