Sum of Column in Query?

[Guest]

I have a query in MS Access 2003. It has two columns (Name and Amount). I
want to know what the total sum is for the "Amount" field in the query. How
do I go about this?

PS (I don't need a running sum total, just a total).

 

[roadie.girl]

http://support.microsoft.com/?kbid=290136
http://support.microsoft.com/?kbid=205183

 

[Guest]

Try

SELECT Sum(TableName.[Field Name]) AS [SumOfFieldName]
FROM TableName

 

[Roger Carlson]

One way is to UNION a Totals query to your original query. Suppose your
original query was like this:

SELECT Table15.Name, Table15.Amount
FROM Table15

Producing this:
Name Amount
Roger 10
Sue 20
Kim 30


Union this to a Totals query:
SELECT "Total" AS Name, Sum(Table15.Amount) AS SumOfAmount
FROM Table15
GROUP BY "Total";

So that the whole thing looks like this:
SELECT Table15.Name, Table15.Amount
FROM Table15
UNION ALL
SELECT "Total" AS Name, Sum(Table15.Amount) AS SumOfAmount
FROM Table15
GROUP BY "Total";

It will produce this:
Name Amount
Roger 10
Sue 20
Kim 30
Total 60


Hope the formatting comes through.
--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Sorry, like I said:

PS (I don't need a running sum total, just a total).

I just need a total for the column:

I.E: If the figures in the total were:

2
2
2
THE TOTAL WOULD BE: 6

How do I get the 6 so to speak? NOt a running total. Just a total.

 

[Guest]

If you want sum per name

SELECT TableName.[Name], Sum(TableName.[Amount]) AS SumOfAmount
FROM TableName
GROUP BY TableName.[Name]

If the field name is Name, consider changing it, because it a reserved name
in Access, it will be fine as long that it will be in square brakets

--
\\// Live Long and Prosper \\//

Try

SELECT Sum(TableName.[Field Name]) AS [SumOfFieldName]
FROM TableName

--
\\// Live Long and Prosper \\//

I have a query in MS Access 2003. It has two columns (Name and Amount). I
want to know what the total sum is for the "Amount" field in the query. How
do I go about this?

PS (I don't need a running sum total, just a total).

 

[Guest]

When I enter this:

SELECT Sum(TableName.[Field Name]) AS [SumOfFieldName]
FROM TableName

I get a the same amount for "Amount" and not a total:

http://www.glensquare.com/screenshot.GIF

(PS, thank you all for helping me on this).

 

[Guest]

It's look like you want to display the sum with the rest of the fields, in
that case use a different approach

Select Field1,Field2,Amount,DSum("Amount","TableName") As SumOfAmount From
TableName

the problem with this SQL, it perform the same DSum for each record, so when
the table will get bigger, the query will get slower
=====================================
Or, another option
create a query that will return only the Sum, as specified in the first post.
Create a second query that contain both, the table and the first query, with
no join between them

Select TableName.*, QueryName.SumOfAmount From TableName, QueryName

 

[Guest]

Ok that worked. Thanks!

Though I think I have run in another problem:

When I Filter by Selection or Filter by Form, the Sum total doesnt reflect
the filtering and still shows the overall total. Any way around that?

 

[Guest]

You need to add the filter to your DSum or extra query (what ever way you
chosed)
If you need help, I need you to post your SQL