Consolidate table

Hi All,

I'm assuming this is an easy question, but it is urgent so I'm assigning it the max points.

I have a table with 3 fields: name, account matrix, amount
The table contains 230307 records.  There can be multiple records for the same name/matrix.

I want to condense this information so there is only one name/matrix combination for each, with the total amount for each combination.  At the end there should be 20-30000 records.

An example:

name                  matrix                amount
cust1                   002                   1.10
cust1                   002                   3.30
cust1                   010                   1.23
cust2                   003                   4.56
cust2                   003                   5.00


Desired output:
name                  matrix                amount
cust1                   002                   4.40
cust1                   010                   1.23
cust2                   003                   9.56


I'd like this information in a text file or excel file (or even natively in access, as I can just export from there).  Unfortunately I'm mostly clueless with access, so I'm turning to you all for help.  I'm very flexible with the output.  I'm going to have to do this a few times (on a few tables, all with the same setup), if that helps.

Please ask if you need any more information!
Thanks
Matt
LVL 35
mvidasAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

harfangCommented:
Hi,

Try this query:

SELECT [name], matrix, Sum(amount)
FROM tblIHaveATable
GROUP BY [name], matrix

Or create the query in the QBE, using the "totals" tool.

Once the query exists, use "File / Export" for text or Excel...

Good luck
(°v°)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jerryb30Commented:
selecct [name], [account matrix], sum(amount) as total from yourtable group by [name], [account matrix]
0
harfangCommented:
Note: if the query runs slow, try adding an index on [name] or on both [name] and [matrix] (dual-field index). This would help the engine.
(°v°)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jerryb30Commented:
Can't beat Markus today.......
0
harfangCommented:
;)
0
mvidasAuthor Commented:
Thanks guys! I'm going to split the points (majority going to Markus) as Jerry's was within a minute of Markus' and would have worked too.
Does adding "as total" after the select do anything special? It worked as I needed it without it.
Also, is there anything I can put in there to have it prompt me for the table at runtime? If not, I don't mind manually editing it, but I figured it can't hurt to ask :)

Thanks again
Matt
0
harfangCommented:
No, prompting for a table name is not trivial.

You would have to do that using VB, and build the SQL string. If it's a one-time thing, you are probably better off doing it manually, but if you think you need it, we can work on that, of course.

Good luck,
(°v°)
0
jerryb30Commented:
AS Total merely applies a field name to the expression when viewing the query.  (If it weren't there, you would see some field name like 'expr1') which is pretty meaningless.

Thanks for the split.  Too kind.

0
mvidasAuthor Commented:
It is an easy enough change for the table name (change "jan" to "feb", etc), so it really isn't worth the time right now.  I'll have to add the 'as total', as it did have a meaningless name (though I exported it without headers, the person I'm helping with this will probably want a normal name).

I am curious about something.. for one customer/matrix combination, there were the following values:

13.8
40.8
13.8
13.8
13.8
40.8
13.8
13.8
40.8
13.8
40.8
40.8
40.8
40.8
13.8
40.8
13.8
13.8
13.8
40.8
13.8
13.8
13.8
40.8
13.8
13.8
40.8
13.8
40.8
40.8
40.8

All ended in .8, the sum of which is 805.80.  However, the query sum'med it up as 805.799999999999. I verified each of the above were actually .8 and not .79999 etc.
Is there any kind of round function I can add into the query?
0
mvidasAuthor Commented:
I should add that I noticed this because the exported text file had it as 805.79.  Out of 50,000 condensed records (my guess of 20-30k was way off), this must have happened 61 times, as it is .61 short of the total ?
0
harfangCommented:
Your field has the single type. The implicit conversion from Single to Double is bad. You can use this:

    Sum(CDec(amount)) As Total

instead. If you are interested, you can read http:Q_21204203.html for kicks ;)

Else, it's of course possible to round the result:

    Round(Sum(amount), 1) As Total

For one decimal.

Cheers!
(°v°)
0
mvidasAuthor Commented:
Thanks Markus! I love most information, so the first few posts I read on your question there looks like the rest will be some good info as well.

Using    Round(Sum([revenue]),2) AS total   worked great! total matched perfectly

Thanks again to both of you for the very quick responses!
0
harfangCommented:
Glad to help, success with your project!
(°v°)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.