how do I add an addition expression in query?

A

ANEWB

I am a self tought user of access... Im eager to learn expression building
please help me some. I have built a db on my job and need to add
calculation(s) where and how do I achieve this aggrivating task....for
example:

TABLE:
1st amount 2nd amount Total
$ 33.00 $33.00 $66.00

This is my table set up but Im not getting a total is displays $0.00....!
I have went to the query and included these three fields. Not knowing how
expressions work Im in the dark.....

Query:

1st amount 2nd amount Total
$ 33.00 $33.00 $0.00

this is my result!!!!!
I have tried to put the expression every place I could think of without
getting an error....no such luck!

Please someone assist me
 
J

Jeff L

By the way, it is not a good idea to save the results of a calcualtion
in your table. If any of your values change, then your Total is wrong.
Eliminate the Total field from your table and you will have better
luck.


Jeff said:
Put this in a field in your query:
Total: [Amount1] + [Amount2]

Hope that helps!

I am a self tought user of access... Im eager to learn expression building
please help me some. I have built a db on my job and need to add
calculation(s) where and how do I achieve this aggrivating task....for
example:

TABLE:
1st amount 2nd amount Total
$ 33.00 $33.00 $66.00

This is my table set up but Im not getting a total is displays $0.00....!
I have went to the query and included these three fields. Not knowing how
expressions work Im in the dark.....

Query:

1st amount 2nd amount Total
$ 33.00 $33.00 $0.00

this is my result!!!!!
I have tried to put the expression every place I could think of without
getting an error....no such luck!

Please someone assist me
Click to expand...
Click to expand...
 
A

Allen Browne

In query design, type this into the Field row of your query:
Total: CCur(Nz([1st amount],0)) + CCur(Nz([2nd amount],0))

The query will not show the total correctly. If you have a Total field in
your table, remove it. You do not store calculated results in a database.

For more information, see:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

The Nz() is necessary in case the field is left blank. The CCur() is
necessary to convert the value to Currency, because JET 4 (Access 2000 and
later) regularly treats the results of Nz() as text, which won't work
correctly.

Ultimately, the best solution is not put repeating fields into a table like
that. Instead, create a related table where there can be many records
instead of many fields. For an example, open Northwind, and see how the
items of the order are put into a related table (Order Details.)
 
Top