Find a SUM

[jcsports31]

Does access allow you to find the sum of numerous rows of data, like excel does? I have 50k rows of data in excel, with multiple accounts that need to be added together. For insance:



ACCT # Value
1 2
1 10
1a 3
1a 15

Does access have a program or feature that would allow me to accumulate a SUM for the value of the accounts?

Thanks,

[ajetrumpet]

SUM() and DSUM() both work.

in a query, using SUM() requires using the GROUP BY clause on every field that the function is not being used on. the plus side to this is that your data will not be duplicated or redundant.

DSUM() is good for one record only in a query (usually, unless you are combining with the DISTINCT keyword for unique records). the downsize to this is that if you have more than one record returned in the query, DSUM() will duplicate the range's (field's) sum in every record.

[NTC]

and while the AJETrumpet post is entirely correct - I'll also add for the novices out there to look at your textbook in queries section for the AggregateQuery. In the query design mode it is the epsilon E symbol in the tool bar. Access makes it very easy to do sums in queries this way.

[ajetrumpet]

and while the AJETrumpet post is entirely correct - I'll also add for the novices out there to look at your textbook in queries section for the AggregateQuery. In the query design mode it is the epsilon E symbol in the tool bar. Access makes it very easy to do sums in queries this way.

NTC,

That is great. Thanks for adding it. I don't think domain aggregates are options though with the summation line, are they? DSUM might have to be written out, which is what I think he needs here rather than grouping.

[centaurr]

I have a question very similar to the OP. I have two columns of information just like OP's and need to output sums of the value column per ACCT#.

ex of table:
acct / value
A / 1
A / 2
B / 2
B / 3

end result:
A = 3
B = 5

Any thoughts?

[ajetrumpet]

not a thought, but here's the correct answer:

SELECT col1, sum(col2)

FROM ...

GROUP BY col1

[centaurr]

how can I apply what you've given me to output the sums and ACCTs in a textbox on a form?

the columns are being displayed in a subform.

TIA

[centaurr]

nevermind.

[centaurr]

thanks for your help ajetrumpet