Query won't total...

  • Thread starter Thread starter kfogle
  • Start date Start date
K

kfogle

Microsoft Access 2003

I have a query which is pulling from several numerical fields. Each
field individually shows up fine, but when I try to get a total of
several of the fields together the total field ends up blank. Here
is the formula I have in the query:

Total: ([0111LICN]+[0110OTHER]+[0112CLAS]+[0113ADMN]+[0114MNGR])

I have tried adding up just 2 of the fields and that works for 50% of
them, but the other 50% are still blank.

I have also tried entering the table name for each field within the
formula but nothing seems to be working.

Has anyone run into this issue before, or can anyone offer any advice?

Thanks.
 
Microsoft Access 2003

I have a query which is pulling from several numerical fields. Each
field individually shows up fine, but when I try to get a total of
several of the fields together the total field ends up blank. Here
is the formula I have in the query:

Total: ([0111LICN]+[0110OTHER]+[0112CLAS]+[0113ADMN]+[0114MNGR])

I have tried adding up just 2 of the fields and that works for 50% of
them, but the other 50% are still blank.

I have also tried entering the table name for each field within the
formula but nothing seems to be working.
Click to expand...

If any one of the fields is NULL, the sum will be NULL: since NULL means "this
value is uninitialized, unspecified, unknown" then any expression calculating
with NULL returns NULL.

The NZ() - Null to Zero - function will solve this:

Total:NZ([0111LICN])+NZ([0110OTHER])+NZ([0112CLAS])+NZ([0113ADMN])+NZ([0114MNGR])

However, a table design issue is pretty apparent here. It looks like you're
storing data - categories such as LICN, OTHER, ADMN - in the fieldnames. What
will you do if you need to add a sixth category? Redesign your tables, forms,
queries, reports? Ouch! Consider having a table related one-to-many to this
table with one *record* per category instead of one *field*. A Totals query
then won't have this problem.

John W. Vinson [MVP]
 
Back
Top