Hi,

Im trying to create a database for a stock control system. I have a slight problem, the stock is in two places. So what i have to do is put in the values of stock in each place and then display the value of those two added up in the table. What is the best way to be able to do this?

It is important that you do not create a calculated field in your table. If you can get a data using others, then that data should not be stored. The use of a query will get you the data needed.

You don't need to store the value in a table.

Just calculate the stock level in a query when you need it

Col

A friendly warning! Stock control (or inventory as our colonial cousins call it) is deceptively complex. Do a search in these forums for stock control or inventory. You will find lots of help and advice. Don't repeat the mistakes that many others have aleady made!

Good luck!

i have now created a query but it still wont work. For the total i have used this,
"number1"+"number2"
number 1 and 2 are the field names i have used. But it still wont add the two values up that there in those columns. What do you suggest?

Use the Sum() function


Col

i have used this,
SELECT SUM (number1, number2) as "total"

but i still cant get it to work. can you please help.

Are [number1] and [number2] both fields in the record?

yeh number 1 and number 2 are the field names in the table

Can you post your whole SQL string, exactly as you wrote it in your database, so that we can take a look at it?

SELECT SUM (number1, number2) as "total"
FROM stock

that is what i have tried to use.

i have also just tried

=Sum([number1]) + ([number2])
but that didnt work

What was the result of the calculation vs what it should have been?

These should do the job...

SELECT Sum([number1]+[number2]) AS total
FROM stock;

or

SELECT Sum([number1])+ Sum([number2]) AS total
FROM stock;

Edit: Maybe someone could tell which one would be faster. I would go with the first one since it has only one function called, but I haven't tested it.