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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Can't beat Markus today.......
ASKER
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
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
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°)
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°)
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.
Thanks for the split. Too kind.
ASKER
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?
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?
ASKER
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 ?
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°)
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°)
ASKER
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!
Using Round(Sum([revenue]),2) AS total worked great! total matched perfectly
Thanks again to both of you for the very quick responses!
Glad to help, success with your project!
(°v°)
(°v°)
(°v°)