Calculations and updating tables

[imhiya]

Ok, i have two tables,

one called Feed Schedule, in it is has Type of food and Quantity Fields

So it looks like this

Type of Food Quantity
Cat Null
Dog 2


Then i have another table called Stock, it has the same layout but has the current stock for each of the above and looks like this

Type of Food Quantity
Cat 2000
Dog 2000

What i am wanting to do is update cat and dog quantitys in the stock table with the stock removed, so it would result with

Type of Food Quantity
Cat 2000
Dog 1998

I want it to do this when i either run a macro or a query using sql. But i am sure how to do this.

 

[imhiya]

any advice would be appreciated mainly i want to know if its possible.

 

[Andrew_w]

I would like to know as well to be honest!

 

[georgedwilkinson]

How 'bout:

update Stock inner join [Feed Schedule] on Stock.[Type of food] = [Feed Schedule].[Type of food]
set Stock.Quantity = Stock.Quantity - [Feed Schedule].Quantity;

This is air code (i.e. untested) and only presented for the concept. You could have done this more easily with the visual query writing tool.

You'll save yourself a lot of trouble by naming objects (tables, columns, queries, forms, etc.) without spaces or special characters.

 

[Andrew_w]

Right, and where exactly does that formula go? Is that the exact formula btw even the:
update Stock inner join

 

[khawar]

You should not be storing such value when these could be calculated on the fly what if some one runs the query twice

 

[georgedwilkinson]

Right, and where exactly does that formula go? Is that the exact formula btw even the:
update Stock inner join

It's "air code"...that means I didn't test it so I don't know if it works. It is an update query and would be typed in to the SQL screen of the query editor, though it could be used in a VBA sub/function with some tweaking.

To reiterate, you could have produced this result in the visual query editor more quickly than the time we have all spent typing in this thread.

Go to the queries objects "tab" on the database window, double click on "Create query in design view", select your two tables, select the field you want to update, click on Query|Update Query, type "Stock.Quantity - [Feed Schedule].Quantity" into the Update To: field. Display the results to make sure it is right and press the "!" when you're ready to do the update.