Question Table structure for multi location warehouse

[Wansa]

Hello all,
I am working with Access 2007. I have a pretty good working knowledge of access. Here is my problem. I have a warehouse which supports four departments. The warehouse floor area is segragated into four areas. Area 1 department 1, area 2 department 2, and so on. Each department uses the same commodity, so I will have four of each item, belonging to each department. The storage locations will be unique and not repeated anywhere in any department. I receive and issue items for all departments. The problem is how do I structure the tables and queries to receive an item for department 1 adding it to department 1 only and not have the item added to another department with the same item. Also to issue the item from a department 1 without effecting the on hand balance of any other department. I have been pulling my hair out trying to figure this out. I am too the point of creating 4 separate databases one for each department.
:banghead:
Need help very bad.
Wansa

 

[plog]

From what you've described this should be your inventory table:

InventoryTransactions
Trans_ID, autonumber primary key
Item_ID, number, foreign key to Item table
Dept_ID, number, foreign key to Department table
Trans_Date, date, date of transaction
Trans_Quantity, number, amount of inventory
Trans_Notes, text, any special notes about transaction

If you want to designate what type of transaction because you have more than two ( e.g. Daily Use, Damage, Transfer, Purchase, etc.) you could have a Trans_Type field which would be a foreign key to a table which describes the transaction and how it effects inventory (+ or -).

 

[Wansa]

Hello Plog,
Thank you for the quick response. Now let me make sure I understand what I need to do. It appears that the Dept_ID number is used to keep parts separate. On my inventory transactions form I will need to add the field Dept ID, which must be populated for any and all transactions. Thanks, you are keeping me on track.

 

[MarkK]

This might be like an accounting transaction, with a header and details. The date/time is in the header, and the TransID, but then the detail records can debit and credit different quantities of different items to and from different departments. So TransID 1, on 4/30/15, post +5 widgets to Dept1, and post -5 widgets to Dept2.

So then for a balance, you just sum the widget posts to a particular department, up to a certain date, and that is the balance for that item on that date. Simple, accurate.

 

[plog]

On my inventory transactions form I will need to add the field Dept ID, which must be populated for any and all transactions.

Correct. If you want inventory by Department, then you need to capture the department where inventory is.

 

[Wansa]

Hello Plog,
YOU ARE THE MAN!!
Thank you and.... HOOAH!!
I am now the greatest thing since sliced bread,
Two thumbs up!!

 

[Wansa]

Hello Plog,
Need help again. while testing the DB the inventory stock levels report is not grouping the same part numbers together to display a total on that part number in a department. Example: boots of the same part number are entered three times in the same day for the same department of different amounts. the inventory stock levels report should group the same part number, for the same department and display one total for that specific part number in that department. Instead the report is displaying all three transactions for the same department. how is the query put together?

Thanks
Mike.

 

[plog]

The table structure I laid out was to keep track of inventory for you at a grainular level: 5 added, 2 used, 2 used, 5 added, 3 used.

What you just described is a system is one where you do the entire inventory outside the system and then enter its level at various times: 5 am 5 on hand, 7 am 3 on hand, 9 am 1 on hand, 11 am 6 on hand, 1 pm 3 on hand.

Which system are you trying to use? Are you telling the system what the entire inventory is at a moment in time? Or do you want to use the system so you can determine what the inventory is?

If you could demonstrate what you want with data like I did it might be more illustrative.

 

[Wansa]

Keeping you up to date. The database is working well.
Thanks again to all.