View Full Version : How to calulate the value of a field in Access...?

14-03-2002, 03:00 PM

Same setup as most of you will remember from last time, I've got a table containing the list of parts for a computer, and each field has a lookup to point it in the direction of the correct component table. For example, when I go to choose the CPU, it gives me a list box of all the CPUs in the table called CPUs.

The CPUs table also contains the prices of the CPUs, as do the tables for motherboards etc etc...

I want to have a column in the computers table called 'SystemCost', that will add up the cost of each component. For example, if my CPU is worth $300, and the motherboard in the next column is worth $290, I want the 'SystemCost' column to add the two together. The value for the cost of the CPU is stored in the CPU table, so the 'SystemCost' column needs to get it from there, and choose the correct price in relation to the CPU I've choosen.

Here's an example.

There are 5 CPUs in the 'CPUs' table, call them CPU1-CPU5, and they are priced at $100, $110, $120 etc.

On my 'Computers' table, I choose CPU2, which costs $110. The 'SystemCost' table needs to display $110, and add it to the appropriate values from the other tables.

Can you see what I'm trying to do?

Thanks in advance...



15-03-2002, 12:34 AM
Hi Erin,

You don't add things in a table - you add them in a query.

Open a query, add the fields you want then in the next empty column put the formula.
Your field names will be different but to give an idea of requirements:

price: [number]*[cost]

(or in your case SystemCost: [CPUcost]+....)

15-03-2002, 08:20 PM

My system suffered a major hard drive failure today, so I won't be able to try it out for a few days...

Got a new HDD coming on monday (another #$%^#$ $260...) and I'm temporarily on a 2GB drive split between linux and Windows, so there's no room for access...

However, I remember trying more or less what you've suggested, but it was flawed because all it did was give me a list box in which I could choose the price of the component. Then I need to add them up, and get that info onto my webpage (which might not even exist anymore, because I'm a cheap-arse with no backup).

Using the SQL you mentioned above I need to put in a 'WHERE' clause, telling it to get the price of [CPUs].[AMD Duron 1000mhz], rather than a list box of all the price of all the CPUs... Do I make any sense?

I'll try again:

Your suggested SQL reads:

SystemCost: [CPUcost]+[MotherboardCost]+etc etc...

However, I need to get (in SQL terms, though I don't think this'd work):

[CPUCost] WHERE CPU = 'What I've specified in the list box'

Can I do something like that?