Sums for each record in a query

[Guest]

I am trying to sum 12 fields for each record in a query. For example, record
1 has a field for each month and in that field is a number. Each record
represents a product and each number represents the number of that product
produced within a specified month. So, my question is, how do I put an
expression within a query that sums those 12 fields for each record?

Thank you.

 

[Rick B]

add a new column to your query with the following in the box where you'd
normally enter a field name...



SumProduct: [Field1] + [Field2] + [Field3]......

 

[Duane Hookom]

Or, you could normalize your table and use a regular totals query.

--
Duane Hookom
MS Access MVP
--

add a new column to your query with the following in the box where you'd
normally enter a field name...



SumProduct: [Field1] + [Field2] + [Field3]......

I am trying to sum 12 fields for each record in a query. For example, record
1 has a field for each month and in that field is a number. Each record
represents a product and each number represents the number of that
product
produced within a specified month. So, my question is, how do I put an
expression within a query that sums those 12 fields for each record?

Thank you.

 

[Guest]

Thank you, Rick B. I was trying to follow the examples help gave me with
parentheses and stuff, then the expression builder, all I was getting was
errors. Your solution did the trick. Thank you again!

add a new column to your query with the following in the box where you'd
normally enter a field name...



SumProduct: [Field1] + [Field2] + [Field3]......

I am trying to sum 12 fields for each record in a query. For example, record
1 has a field for each month and in that field is a number. Each record
represents a product and each number represents the number of that product
produced within a specified month. So, my question is, how do I put an
expression within a query that sums those 12 fields for each record?

Thank you.

 

[MGFoster]

I am trying to sum 12 fields for each record in a query. For example, record
1 has a field for each month and in that field is a number. Each record
represents a product and each number represents the number of that product
produced within a specified month. So, my question is, how do I put an
expression within a query that sums those 12 fields for each record?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your table is not normalized. You may want it like that. If you like
it that way then the query is more complex:

SELECT product_name,
Nz(col1)+Nz(col2)+Nz(col3)+...Nz(col12) As TotalProduced
FROM table
WHERE <your criteria>

The Nz() ensures if the column value is NULL it will not NULL the rest
of the calculation.

If the table was normalized you have a query like this:

SELECT product_name, Sum(Produced) As TotalProduced
FROM table
WHERE <your criteria>
GROUP BY product_name

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmaOgYechKqOuFEgEQIdwgCglKeO9N065TrAF7hPIs/K8NKioLIAmwVm
LQdWwswx04IJ9wGH5fIbisd6
=+FiZ
-----END PGP SIGNATURE-----

 

[John Vinson]

I am trying to sum 12 fields for each record in a query. For example, record
1 has a field for each month and in that field is a number.

Then your table is incorrectly designed. Storing data - a month for
example - in a fieldname is not correctly normalized! A better design
would be to have a one (product) to many (date) relationship in a
second table, with fields ProductID, ProdMonth (a Date/Time field
including the year, not just the month, so you can turn the corner
from December to January without messing up your data), and an amount.

Each record
represents a product and each number represents the number of that product
produced within a specified month. So, my question is, how do I put an
expression within a query that sums those 12 fields for each record?

TotalAmount: NZ([Jan]) + NZ([Feb]) + NZ([Mar]) + <etc>

John W. Vinson[MVP]