Thread: Please point me in the right direction

It look like you are trying to create a Transaction table here.

First, unless a specific product item can only be stored in one location/bin/lot# combination, you will need to add a Primary Key on the Inventory table, otherwise, you will not be able to relate a specific transaction to the specific location from which the inventory item is pulled. Let's call this InventoryID

With that in place, you can create a Transaction Table (or History Table). I'm going to assume that you only update this table when the inventory item is pulled. You can make it more complicated if you need by keeping the 'state' of the transaction, such as Entered, Approved, Picking, Pulled, Shipped, Backordered, Canceld, etc. You get the idea.

The format of the Transaction Table then could be something like:

Code:

TrasnactionID -- Primary Key
Inventory ID -- Location from which the item is pulled
Qty -- Number of items pulled
Date -- Date the items are pulled

Then, when an inventory item is pulled, that data is saved in the database. Since you have Qty in the Inventory table as well, do forget to update that field.

When you need to report on the History of a particular item, these three tables would be JOINed to produce the history information.

You may want to create a Transaction record for when inventory is replenished as well.

I'm not an Access developer, more of a data designer; so I can speak to Access directly. However, I don't think I would do it the way you are thinking. Rather, I see an Inventory Pull form that contains a Quantity field. The picker would enter the number of items pulled and when an Update button is click, the application would create the Transaction record and then update the Qty field in the Inventory table; subtracting the value of the Quantity field entered by the Picker. All this would be in the code executed by the Update button click event.

Of course, you have to put some checks in there -- such as not allowing the number of item picked to exceed the quantity on hand, etc.

Coding is a part of life in the DB world. We have some good Access folks here. Perhaps once the weekend is over they can comment here.

This isn't a very hard bit of code, so would probably be a good way to get into the fray. And this site is a good resource if you get stuck.