Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 564
  • Last Modified:

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
0
mvidas
Asked:
mvidas
  • 6
  • 4
  • 3
2 Solutions
 
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
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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