calculating columns (fields)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dino - 17 Feb 2006 22:40 GMT

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.

Reply to this Message

Wayne Morgan - 17 Feb 2006 22:54 GMT

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'));

Signature

Wayne Morgan
MS Access MVP

> I'm trying to calculate columns (fields) in a query in Access. I have to
> be
[quoted text clipped - 17 lines]
> can
> help. I have not been able to figure out how to do this.

Reply to this Message

KARL DEWEY - 17 Feb 2006 22:57 GMT

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.

> 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
[quoted text clipped - 12 lines]
> 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.

Reply to this Message