How to keep a running inventory in Access Database of retail sales

[Bigdaddrock]

I have a database similar to Northwind Traders. I need to reduce the INVENTORY Field of my PRODUCT Table daily, by the number of Products sold each day. (In essence I am keeping a running inventory - which my client requires.)

I have two TABLES called PRODUCT and ITEMS:

The PRODUCT Table contains fields called PRODUCT ID, INVENTORY and DATE LAST REVISED (as well as others).

The ITEMS Table contains fields called DATE SOLD, PRODUCT ID, VOLUME SOLD (as well as others).


I have four QUERIES:
Query 1 draws from the ITEMS Table. It lists what PRODUCT IDs and associated VOLUMES SOLD have occurred since the latest DATE LAST REVISED on the PRODUCT Table (a criteria input by user).

Query 2 draws from Query 1. It sums up by PRODUCT ID the volumes of PRODUCT SOLD since the latest DATE LAST REVISED on the PRODUCT Table using Query 2 as its source.

Query 3 draws from Query 2 and the PRODUCT TABLE. It computes a REVISED INVENTORY for just those PRODUCT IDs that appeared in Query 2, by subtracting the UNITS SOLD from the INVENTORY level then in the PRODUCT Table. I have also created a field called DateOfUpdate, which is populated with the expression Date().

Query 4 is an UPDATE TABLE. I am trying to Update the REVISED INVENTORY values to the INVENTORY field of the PRODUCT Table, and the DateOfUpdate to the DATE LAST REVISED. However, each time I try to view the table prior to executing the UPDATE, I get nothing but the old INVENTORY levels and no date at all in the second field.

Any suggestions as to what I may be doing wrong. Or any suggestions on how to better design a way of keeping track of my Inventory? Thanks for the assistance.

[Jerry Maiapu]

Post the SQL string of query 4 (update query)

[Delerna]

Its not stated but I presume Query4 is using the data from Query3 to update the table ?

When you say you are trying to view the table prior to executing the update do you mean by running Query3 ?

If so then the SQL code for Query3 needs to be posted, in fact an answer would probably arrive quicker if you posted all 4 queries