Adding many fields in a query

[coastal]

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.

 

[Wayne-in-Manchester]

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/

 

[coastal]

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.