Inventory Transaction Problem

  • Thread starter Thread starter Bob1866
  • Start date Start date
B

Bob1866

Hi, I am using the Microsoft Inventory Template and the Order Management
Templates which I have combined into one database, therefore the Add an Order
and details Form now looks in the products form from the inventory side etc.
Everything is working except when I put a new order through I need the
Inventory Transactions to add the transaction as a sale, hence the Units on
hand would decrease by qty sold.

I have created a query to show the order details i.e Order Date, Product Id
and Qty Sold, and here is the problem - how do I get the Inventory
transactions table to update from this query.

I have tried using an "Append Query" but every time the query is run it adds
all the transactions to the table again, I have tried using the "update
Query" but this doesn't seem to work (I don't actually need to update
existing records I just need to add the new record of the sale) Is there any
criteria I can put in the query to make this work or is there another way of
doing it?

Many thanks for any help, it will be much appreciated.

Windows XP Professional
Office 2003
 
I'm not familiar with that specific template, but I don't understand why you
even need a Units on Hand field in your database.

The Units on Hands always equals the Units Purchased minus the Units Sold.
The exception would be for items spoiled or stolen, and an entry should be
made in the Units Sold field, (or a new field, which will always be
subtracted as well).

It is against database normalization rules to permanently store a calculated
value. There are rare occasions where it may be necessary, but this isn't
one of them.
 
Back
Top