Inventory nightmare

[smeghead67]

Hello,



I am working on a inventory management DB for a casino. my basic concept is to be able to have a total of each kind of part, and be able to tell where the parts are stored. there are more functions to be added but this part is causing me grief.

i have 7 unique storage areas including a repair bench. each of those have racks, shelf's and bins with parts.

The easy way i see to do this is simply create a table.

partstorage
PSID - Primary Key (autonumber)
partID - Foreign Key to part in part table (Number)
PSA - PartstorageA - Number of parts total in area A (Number)
PSB - PartstorageB - Number of parts total in area B (Number)
Etc...
PST - PartstorageText - a text or memo field that will hold the info of what rack, shelf and bin parts are on.

Parts can be moved between the storage areas or used in a machine. I could create a form to look at those totals and create buttons to move from one to another. But i wouldn't know how many are in rack 1 shelf 4 bin 3

What i want is that, to know how many are in a certain bin. Do i need to create 3 tables?

parts, Binlocation and partstorage

Parts
partID - PrimaryKey (Autonumber)
mypart - Identifer we refer to it by (text)
etc

Binlocation
bid - PK (Autonumber)
binid - the representation area, rack, shelf & bin

partstorage
psid - PK
bid - FK
partID - FK
qty - the quantity of parts in this bin (number)

I will want to do a total query for a certain part in each area and the total amout of a part.

this is only maybe 25% of the total project but it is what is currently giving me fits. I await your wisdom.

Ted

[orange]

There was a post a while back that dealt with equipment in Inventory and the poster wanted to move equipment to and from Inventory from various locations.

THe thread is at
https://www.accessforums.net/forms/h...orm-24504.html

I created an access database in mdb(2003) format that simulated his problem and offered a possible solution (or at least some concepts that would help him).

That database is attached to a post within that thread. It may be helpful to you. It really only involves moving equipment between locations and knowing what is where at any time.

But note that Inventory management encompasses a vast number of concepts and can be quite simplistic or can get extremely complex. Different people mean very different things under the term/name "Inventory Management"

Good luck with your project.

[suttilld]

Just a thought, but you could turn the data structure around, instead of having a table with part locations referencing different parts, why not have a table of parts with a location filed that references a table of locations. That way you could easily query the parts table to count the total number of for parts of type X in location Y.

[smeghead67]

Orange,

I looked at your DB it would be good if i only had large items like printers or bill acceptors which can have unique serial numbers and can be repaired, which i do, but i also have push buttons, capacitors and light bulbs that i just need a total of so I don't run out.

I may have to create a new entities for reparable parts (Assets) and non-repairable or disposable parts (Inventory) and while counting them treat them differently.

Ted

Ted

[smeghead67]

Suttilld,

I will have the same part at multiple locations. our casino has a main building and a second one with a gaming floor 3/4 of a mile away we keep a minimum of parts there for use on the floor, but have 3 storage areas to pull stock from.

Ted

[orange]

I suggested you look at the database only to get an idea of some of the related questions posters have asked and a database to address the problem/situation (at least in a simulated manner).
The previous poster wanted to swap primary keys or some technique that sounded awkward. I thought moving equipment should be more straight forward, and created a simple set up to show the concept. It was for demonstration, to show the tables and relationships, to show a form and various buttons to perform certain processes, and a log file to show what was moved where and when etc.

Some of the concepts and approaches may apply to your situation, but maybe not.

You could create entities that have a StockonHand count and a transaction approach that shows all items from stock as negative, all additions to stock from supplies/suppliers as positive. At any point in time you should have a degree of certainty of the StockOnHand of any item, provided that your manual and computer processes are tied together in some coherent fashion, and you institute some form of stock taking on a regular basis. A prudent degree of checks and balances to assure a "reasonably accurate count" will be required.

Good luck with your project.

There are existing free datamodels at http://www.databaseanswers.org that may be of use to you.

Also there is a basic methodology to create a data base. The basic approach can be found here
http://www.databaseanswers.org/approach2db_design.htm