how do I add an addition expression in query?

[Guest]

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

 

[Jeff L]

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

Hope that helps!

 

[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.

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

 

[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.)

 

[Jamie Collins]

Total: CCur(Nz([1st amount],0)) + CCur(Nz([2nd amount],0))

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.

Considering the Access function Nz() is alien to Jet 4.0, I think the
blame must be placed on Access, because its function is unintuitive
(e.g. recasts a column's data type?), or perhaps the user, because
they've used Nz() incorrectly e.g. this would seem more logical:

Nz([1st amount], CCur(0)) + Nz([2nd amount], CCur(0))

but I can't say because I am not familiar with NZ(); I prefer to use
something that isn't alien to Jet e.g. so that SQL code in VIEWs/PROCs
doesn't error when invoked from outside out the Access user interface
(think Excel users in the enterprise wanting to import data safely by
going via the database developer's queries) e.g.

IIF([1st amount] IS NULL, CCur(0), [1st amount])
+ IIF([2nd amount] IS NULL, CCur(0), [2nd amount])

i.e. I would only explicitly cast the zero because I know Jet's IIF
would honour the columns' respective data types.

Jamie.

--