formula for calculating 12 columns in a record

[MomMom]

I'm very new to Access so please be patient with very simply questions.
I'm running Access 2003 and have a database with approximately 52 fields.
I have 10 very simple formulas:

[Field_A]*[Price_A]
[Field_B]*[Price_B]

etc, etc. They are all running fine with the total of each formula going into a field right next to the price field. Expr1[Field_A]*Price_A].

My problem is after these 50 fields I need a total field that will add all the previous total fields A-J. My formula looks like :[expr1]+[expr2]+[expr3]+[expr4]+[expr5]+[expr6]+[expr7]+[expr8]+[expr9]+[expr10] (I did not name the fields, just left them at expr1-10).

And I get nothing!! Please can anyone tell what I am or am not doing and how do I get this working? Thank you

 

[CJ_London]

Do you actually have

Expr1[Field_A]*Price_A]

or

Expr1: [Field_A]*[Price_A]?

and do you have a straightforward select query (i.e. not grouped, inserting, crosstabbing etc)

 

[MomMom]

I actually have Expr1:[Field_A]*[Price_A] and it is a straighforward select query

 

[recyan]

Do all your fields (Edit : Field_A,Field_B,...., Price_A,Price_B,.....) have values in them ?

Thanks

 

[MarkK]

MomMom, Google "database normalization." You seem to be using a database table as if it were a spreadsheet, and that's really really not how a database table works.
Your expression [expr1]+[expr2]+[expr3]+[expr4]+[expr5]+[expr6]+[expr7]+[expr8]+[expr9]+[expr10] would never occur in well designed table. Instead, each of those values would be in it's own row, and you could sum them using SQL like ...

SELECT Sum(Expr) FROM tblExpr WHERE ExprID < 10

hth

 

[Guus2005]

MomMom, Google "database normalization." You seem to be using a database table as if it were a spreadsheet, and that's really really not how a database table works.
Your expression [expr1]+[expr2]+[expr3]+[expr4]+[expr5]+[expr6]+[expr7]+[expr8]+[expr9]+[expr10] would never occur in well designed table. Instead, each of those values would be in it's own row, and you could sum them using SQL like ...

SELECT Sum(Expr) FROM tblExpr WHERE ExprID < 10

hth

First i'd use the solution Lagbolt mentioned to normalize your database.
Second, when you want to add multiple values and one of the values is NULL, the end result is always NULL.

Use the Nz function to circumvent this problem: Nz([expr1],0) + Nz([expr2],0) + etc.

HTH

 

[scubadiver007]

When designing tables think "long and thin" rather than "shallow and wide"



So every score you are trying to sum is stored in a separate record and not field. Access can deal with thousands of rows but not many fields.

Don't worry too much, the best way to learn is the hard way and get it all wrong then you will "get it".