asked on

Auto update field in a table Access 2007

Hi experts
My data base contains 3 tables we will call them "IntoStock", "OutOfStock" and "CurrentStock"
IntoStock contains records of all items ordered for stock, OutOfStock contains records of everything taken from stock and booked to a job.
I want to automaically update the current stock quantitly when anything is booked in or out of stock. I have tried the update query method using DSum where the item descriptions match, however the error "Operation Must contain an updateable query" comes up so I guess the syntax is wrong or even the whole principal of what I am trying to do.
Please assist me!
Thank you

Scott McDaniel (EE MVE )

Generally you should not store data in this fashion, at least for a typical stock/inventory system. Instead, you'd store all transactions in a single table (using a positive transaction for a stock intake, and a negative transaction for a stock outtake) and then calculate your current quantity based on that table (and, perhaps, other tables if need be). Also, storing the "current" quantity is a very bad idea, since it must be consistent with at least 2 other tables (in your case, anyway) - and that's not likely to happen.

Here's Allen Browne's excellent example of a proper inventory system:
http://allenbrowne.com/AppInventory.html

Helen Feddema

I agree. With very rare exceptions, queries should be used for calculated values.

Tintoman51

ASKER

I'm obliged for the replies
So now I have a single table which contains all the inbound stock recorde and all the outbound stock records.
I can query the the inbound and outbound totals by product code or description and filter them further to "inbound" or "outbound".
I have the total results displayed on my form but how on earth can I subract the outbound figure from the inbound?
if I try to set the total data control source to inbound - outbound nothing happens unless I click on both the inbound and outbound controls.
I am lost!

Scott McDaniel (EE MVE )

How did you store your transactions in your table? If you stored those as positive and negative values, then a simple DSum would work to get your Total:

DSum("YourQuantityField", "YourTransactionTable", "WhereClauseIfNeeded")

If you did NOT do this, then you may need more complex summations. If you included a field where you can define a transaction as "inbound" or "outbound", then you will find this much more difficult to work with.

Tintoman51

ASKER

I see the sense in that
I would like the users of this database to be able to type in the quantity they draw from stock without having to put put the "-" sign in every time. Can you tell me how to make the Quantity a negative by default?

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tintoman51

ASKER

Superb! all working thank you