calculating columns (fields)

[Dino]

I'm trying to calculate columns (fields) in a query in Access. I have to be
able to do this to display the correct data. My table looks like this (simple
example):

ID # STATE STATUS AMOUNT
123 CA current $100.00
222 AZ delinquent $200.00
115 CA delinquent $150.00
444 AZ current $120.00
999 CA current $90.00

What I need to be able to do in a query (or table) is to calculate different
totals based on various criteria, like the total dollar amount of all
delinquent accounts in CA, or the total dollar amount of all current accounts
in AZ. Can this be done in a query or table in Access? Thanks, if anyone can
help. I have not been able to figure out how to do this.

 

[Wayne Morgan]

This can be done in a query or by using the aggregate function DSum().

Example:
DSum("Amount", "TableName", "State = 'CA' And Status = 'Current'")

will return the sum of all current records in California.

As a query, this would look like:

SELECT Sum(TableName.Amount) AS SumOfAmount
FROM TableName
WHERE (((TableName.State)='CA') AND ((TableName.Status)='current'));

 

[KARL DEWEY]

Yes.
Create a new query in design view, drag down STATE, STATUS, and AMOUNT fields.
Change the query to a Totals query by clicking on the icon that is the Greek
letter Epsilon on the tool bar.
The grid will chage so that the third row is label Total:
Change the Group By under AMOUNT column to SUM.

Save and run the query.