Totals in a Field

  • Thread starter Thread starter Lamar
  • Start date Start date
L

Lamar

I am have constructed a database table with fields
listing checks written on different bank accounts to
different vendors. I would like to be able to total each
column. The total may be in the report or a query.

The Sigma operation doesn't give me what I need or I am
not using the operation correctly.

Any help would be appreciated.
Lamar
 
Hi:

In a form you could create a field with RecordSource like this --
[Field1]+[Field2]+[Field3]

This assumes all data is on a single record.

If data is on multiple records then do a query like this

Select Sum([Field1]) From [Table1] Where [SomeField]=SomeCondition

Here we Sum Field1 from Table1 for a Condition

Regards,

Naresh Nichani
Microsoft Access MVP
 
The Sigma icon does not have the same result in Access that it has in Excel.
Suggest that you experiment with it in SQL view to see the resulting SQL
string.

Suppose the following table with FieldA and FieldB is valid.

A, 2
A, 2
B, 5

You want to add the FieldB values and display a result equal to 9.

A simple SELECT query

SELECT tblMyTable.FieldA, tblMyTable.FieldB
FROM tblMyTable;

returns three records above.

Clicking the Sigma icon changes the SQL string to:

SELECT tblMyTable.FieldA, tblMyTable.FieldB
FROM tblMyTable
GROUP BY tblMyTable.FieldA, tblMyTable.FieldB;

returns:

A, 2
B, 5

but what you wanted is:

SELECT Sum(tblMyTable.FieldB) AS SumOfFieldB
FROM tblMyTable;

(In the QBE window, remove FieldA, leaving only FieldB, and change the
"Total" line from "Group By" to "Sum"

returns one record:

9
 
Back
Top