Adding many fields in a query

[Guest]

First time user. I am a point & click Access user, dont know the code behind
the database.

Have serveral fields in a table that need to be added together to make total
per record. Working in a Query I have the following:
PA: [P1]+[P2]+[P3]+[P4]+[P5]+[P6]+[P7]+[P8]+[P9]
and also
TA: [T1]+[T2]+[T3]+[T4]+[T5]+[T6]+[T7]+[T8]+[T9].
These fields are all in the same table.

That formula is not working.

 

[Douglas J Steele]

If there's a chance that any of the fields might not have a value, you need
to use the Nz function to handle the Null:

PA:
Nz([P1],0)+Nz([P2],0)+Nz([P3],0)+Nz([P4],0)+Nz([P5],0)+Nz([P6],0)+Nz([P7],0)
+Nz([P8],0)+Nz([P9] ,0)

 

[Rick B]

Without knowing what these fields are, what data type, etc. it would be very
hard to answer your question. Also, the term "not working" is kind of
vague.

 

[Guest]

Do you have the same type of data in each field ?

Is there data in each field ?

If so try this (the Nz function simply gives a value of zero if the field is
null)

(Nz([P1])+Nz([P2]+Nz([P3]+Nz([P4]+Nz([P5]+Nz([P6]+Nz([P7]+Nz([P8]+Nz([P9]))
and
(Nz([T1])+Nz([T2]+Nz([T3]+Nz([T4]+Nz([T5]+Nz([T6]+Nz([T7]+Nz([T8]+Nz([T9]))

Hope this helps

 

[Jeff Boyce]

In addition to the other responses pointing out the use of the Nz()
function, when I see a "series" of fields, all containing the same kind of
data ("that need to be added together"), I wonder if you might not be
working with a spreadsheet rather than a relational database?

In Access, a well-normalized table structure would put "repeating values"
(e.g., P1, P2, P3, ...) into a "many" table, pointing back at the "one"
(i.e., parent) table. With this design, you can use Access' built-in
functions like Sum() (which adds DOWN, not across).

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

Yup that was it...thanks!
--
--coastal

If there's a chance that any of the fields might not have a value, you need
to use the Nz function to handle the Null:

PA:
Nz([P1],0)+Nz([P2],0)+Nz([P3],0)+Nz([P4],0)+Nz([P5],0)+Nz([P6],0)+Nz([P7],0)
+Nz([P8],0)+Nz([P9] ,0)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

First time user. I am a point & click Access user, dont know the code behind
the database.

Have serveral fields in a table that need to be added together to make total
per record. Working in a Query I have the following:
PA: [P1]+[P2]+[P3]+[P4]+[P5]+[P6]+[P7]+[P8]+[P9]
and also
TA: [T1]+[T2]+[T3]+[T4]+[T5]+[T6]+[T7]+[T8]+[T9].
These fields are all in the same table.

That formula is not working.

 

[Joseph Meehan]

First time user. I am a point & click Access user, dont know the
code behind the database.

Have serveral fields in a table that need to be added together to
make total per record. Working in a Query I have the following:
PA: [P1]+[P2]+[P3]+[P4]+[P5]+[P6]+[P7]+[P8]+[P9]
and also
TA: [T1]+[T2]+[T3]+[T4]+[T5]+[T6]+[T7]+[T8]+[T9].
These fields are all in the same table.

That formula is not working.

On the chance that you may come back an read this, it would appear that
you may have a bad table design. That has nothing to do with your question,
but maybe you should come back and ask a question about good table design
with the data you are using.