Tracking Inventory Using Tables

jamiek555

New member
Local time
Today, 13:02
Joined
Jul 13, 2002
Messages
9
I think this should be simple...but....

I have a table called "BOOKS" that lists all the books I stock. The "BOOK_INVENTORY" field contains the number of copies I have in stock (e.g. 10 copies).

I have a query called "BOOK ORDERS" that lists the Order No., Order Line No., the book ordered and the quantity ordered.

This query has a calculated field:

NEW_BOOK_INVENTORY:"QUANTITY-BOOK_INVENTORY"

How do I update the "BOOK_INVENTORY" field on the "BOOKS" table with the number in the "NEW_BOOK_INVENTORY" field on the query?

PS - I am not using forms at this point for users to enter their orders - only tables.

Thanks in advance,

Jamiek555
 
Jamie,

Not exactly sure what you want (probably cause I'm reading it diffrently and cant understand why you would want to have one field read the same as another) But, if you have a look at the samples that come with ACCESS, there's an inventory database that may provide some insight. Sorry cant be of more help but I'm sure this sample will help you.
 
You'll have to tell more about your table structure (the tables used in your query as well the relationships between your tables).

As for your orders, are we talking 'bout Sales or Purchasing?

RV
 
Sorry I wasn't very clear. Here's what I am looking at

TblProducts (Contains following fields):

ProductID (key) (for example Product A)
ProductDescr
ProdInInventory (# of product I have in stock, for example 10)

TblOrders (Contains following fields):

OrderID (key)
ProductID ((key - linked back to TblProducts)
CustomerID (linked back to TblCustomers)
QuantityOrdered (for example 3 units)

My query (QryOrderDetail) contains fields from both of these tables that provide details about the products, orders and customer. The last field in the query is a calculated field similar to:

RemainingInventory:TblProduct!ProductInInventory - TblOrders!QuantityOrdered

In the example, RemainingInventory for Product A would be 7 (10 original - 3 purchased).

I would like the TblProduct!ProductInInventory field for that particular item to be updated to 7 units so that I can track inventory on hand.

Hope this makes more sense...

Thanks,

Jamie
 
Set an Update Query in Design View based on tblProducts and tblOrders:-

Field: ProductInventory
Table: tblProducts
Update To: [TblProducts]![ProductInventory]-[TblOrders]![QuantityOrdered]


It should create an Update Statement in the query's SQL View:-

UPDATE tblProducts INNER JOIN tblOrders ON tblProducts.ProductID = tblOrders.ProductID
SET tblProducts.ProductInventory = [TblProducts]![ProductInventory]-[TblOrders]![QuantityOrdered];


Run the query to update tblProducts.ProductInventory.
 

Users who are viewing this thread

Back
Top Bottom