Addition calculation across the record

P

PJ

I give up! I have several tables of budget categories. The tables are
$requested, $awarded & $spent. Each table has the same budget catigories and
are "currency" type fields.
I have a query with 1 table which places each field in the query. The
goal here is to input data on a form and have a running total of the $'s
entered in the various budget categories while performing data input using
the form. So, I have a calculated column in the query with the following
expression.

Total:
[travel]+[housing]+[labmat]+[equip]+[books]+[photocopy(L)]+[photocopy(S)]+[food]+[postage]+[subcomp]+[phone]+[perfvisart]+[other1amt]+[other2amt]

Here's the Mystery. It used to work! ....and now it doesn't. It will
calculate the old data but not any new. What would cause my new data not to
work? (Is there a place where calculations are turned off?) I've been
struggling for days and hope someone in the magic ether has a solution for
me. BTW, I'm not a SQL person. Keep it simple!
 
D

Douglas J. Steele

Can you be guaranteed that each of those fields has a value? If not, wrap
each field with the Nz function:

Total: Nz([travel],0)+Nz([housing],0)+Nz([labmat],0) etc
 
D

Duane Hookom

I would consider this structure quite un-normalized. You have data values
(books, photocopy, postage,...) as field names. I would use these as values
in a field.

However, your issue might be fields with Nulls (no value). In your
expression, if photocopy(L) has no value, then the entire expression will
have no value. YOu could try wrap each field in Nz() ie:

Nz([travel],0)+Nz([housing],0)+Nz([labmat],0)+...

Or, you could normalize your tables so the budget category is a value in a
field rather than a field name.
 
P

PJ

Duane,
Could you further explain how the budget category is a value in a field
rather than the name?
 
D

Duane Hookom

Consider a table with a structure like
tblBudgetAmounts
=================
BudAmtID autonumber primary key
BudgetYear
BudgetCategory values like "Travel", "Housing", "Books"
BudgetAmount currency

You should be able to add budget categories without changing designs of
tables, queries, forms, reports,...
 
Top