Inventory Database - Moving Product within locations

[MilinPCH]

Hello,

I have created an inventory database for my company that successfully tracks the entry and selling of widgets and comes up with a current inventory on hand form/report for the user.

Unfortunatly (for me!) the users have come back and decided (after i finished building it !!) that they would like to be able to track the inventory in our three possible inventory locations, as well as roll up the totals into what I have now.

I have no problem with creating a code/field to represent each location - I suppose I will have to add it to the queries and forms that add inventory and create purchase orders to sell it.

But my question is conceptual - what do you guys think is the best approach to set up a mechanism whereby my users can move 50 widgetA from Location 1 to Location 2 ? Keep in mind that since I build the database with everything coming in and out of one place, my tables and queries aren't set up to do any transferring.

Do I do some type of form that does a delete query and then an append query? Do I have to create some type of additional tables for each location? Can I create some type of "virtual queries" to hold the info? Ultimatley, I want to keep what I have done to see the "big inventory" but have three sub-location inventories within it that add up to the big inventory.

Why can't users know what they want? I asked them so many questions and took months to make this !!!!!!!!!

THanks,
Milin

 

[Guest_imported]

Is this database in three different physical locations? Does this need to be in real-time?

 

[riny]

Whatever your purchase and sales forms/procedures, if you have transfers from one location to another, seems to me you cannnot get around a location ID.

So this is where design/concept comes in.
Basically, company has locations, locations have wickets.
For the transition phase, you could have location "undetermined"
I am sure you can manage that reorganistation of your tables and relationships, and present forms by location subform wicket type, or wicket type by subform location.

How to handle transfers?
Assume location 1 has 100 wickets type x, locations 2 and 3 have none.
You want to transfer 50 wickets type x from location 1 to location 2.

There will be a form by wicket type, subform locations.
location 1 will show 100, locations 2 and 3 will show 0.
In my view the best way would be a button called "transfer stock", where you will be able to program what happens, for a start you will ask new location, and quantity to be transferred controlling that it is not more than available, knowing where the cursor is (on wicket type x location 1) you can ask for confirmation and then update the subform recordsource with less 50 location 1 and +50 location 2, requery the subform afterwards.

Hope I understood correctly the issue............

 

[demoman]

The standard approach is to use an inventory transaction (IT) table to record this type of information, or at least the 'move'. Common transaction types are:

WIP receipt
PO receipt
Issue
Sale
Move
Scrap
Adjust

As for recording the location information, there is usually one, or many tables, which contain the itemID, reference, originalQty, currentQty, unitCost, locationID, etc. The 'reference' is usually a receiptID. I have seen quite a few different schemes used. Since the number must always be unique, they can get rather esoteric and often lengthy. For example:

PONummber & POItemNumber & ReceiptDate & ReceiptTime

If you are using serial lot control, this is also known as the lot number, or batch number.

As for the interface, I would create a form:

User selects item
Available inventory location(s) displayed
User selects from list (if more than one)
User selects quantity - default all? - validate <=
User selects valid new location
dialog to show result of impending 'move' transaction
Commit,cancel

behind the scenes:

record a 'move' transaction in the IT table.
update the Inventory Lot (or whatever) table, to show new location.