How To Make A Field The Sum Of Several Other Fields

[idiotjohn]

Hi,
I am setting up a database for a computer building company, and have nearly perfected my system. The one thing that I can't work out how to do is SUM several fields of records to get the total price for the computer.

Basically my table is like this:
Motherboard Price| CPU Price |...|Total Price
-----------------------------------------------
$80 | $60 |...| ???
$100 | $70 |...| ???

How would I find the Total price from multiple other fields?

Thanks,
John

 

[vvasudev]

In SQL you can use Sum(Field1 + Field2) function.

 

[Galaxiom]

The Total Price is a derived value and should not be stored in the table.

On the FORM enter something like the following as the Control Source property of the TotalPrice textbox. Set the format property as currency.

= [Motherboard Price] + [CPU Price]

BTW. Highly advise you to not use spaces in names for fields, tables or anything else. You can use the spaced names in the Caption property of the control labels and forms but it just makes for greif anywhere else.

 

[georgedwilkinson]

Your design is not normalized. Therefore, you'll have to use cludgy sql syntax or just put a formula in your total field.

The syntax for either would be something like
Field1 + Field2 + ... + FieldN

Very sloppy. What happens if you build a computer with a part you don't already have a column for? What happens if computers evolve (again) and the parts you have columns for don't actually exist any more and new parts do exist?

This baby needs to be normalized. What you have here is a basic bill of materials setup with component pricing.

 

[Galaxiom]

In SQL you can use Sum(Field1 + Field2) function.

No. This would return the sum of the fields over all records.

And yes George is correct. Your design needs fundamental work.

 

[idiotjohn]

Thanks Galaxiom, that worked. Yes, my field names don't actually have spaces in them, i just made a small mistake there typing it in.

And yes, I realise that I have done it in a weird way, i will look into making it right.

Thanks Guys,
John

 

[georgedwilkinson]

I have 2 tables and i want to run an Update statement in ACCESS:

UPDATE FINAL_TEST
SET FINAL_TEST.Sales_Jan = (Select Temp.Sales from Temp where Temp.RowID = FINAL_TEST.RowID),

FINAL_TEST.Units_Jan = (Select Temp.Units from Temp where Temp.RowID = FINAL_TEST.RowID)


Error Message: Operation Must Use an Updatable Query..Please Advice

thank you

This is in no way related to the original post and has already been posted by you in 2 other posts. If someone wants to answer it, they can do it there. In the meantime, I have reported your post as a duplicated to the admins/mods.

 

[vvasudev]

Oops was just trying to get a quick reply..sry if it offended you

BR,
Vineeth Vausdevan