Creating an inventory databae

[DanLarn]

Hello All,

I have come in search of some advice, I am currently trying to establish a more efficient inventory system at work. I created a simple spreadsheet before, but people are wanting more from it now and I feel that a database is the way to go. I used Access many years ago, but seem to have forgotten how to do most things.

Here is how my spreadsheet looks:

UID	Flag	Manufacturer	Role	Item Description	Qty.	Stock Level	Location	Rack	Position	Serial Number	Notes
1		Sonardyne	PIES	Sonardyne DUNKER Cable Reel	1	Acceptable	Wet Stores			N/A	w/ Flight Case
2		Sonardyne	PIES	Sonardyne DUNKER	1	Acceptable	Wet Stores			289949-001	w/ Flight Case
3		Sonardyne	PIES	Sonardyne PIES Flight Case	3	Surplus	Wet Stores			N/A
4		Valeport	TS Dip	Valeport TS Dip Probe	1	Acceptable	Wet Stores			SN:44131	w/ Flight Case
5		Valeport	TS Dip	Valeport TS Dip Probe	1	Acceptable	Wet Stores			SN:44130	w/ Flight Case
6		Shark Marine	TS Dip	Shark Marine 11-24 Cable Reel (Motor)	1	Acceptable	Wet Stores			HRSB-44165	w/ Spectron rope for TS DIP
7		PBX Systems	PosNet	PBX Systems PosNet RF Pod	1	Acceptable	Wet Stores			90539	Barovane rGPS pod
8		BARO	Wide Tow	BAROGenerator propeller	1	Acceptable	Wet Stores			N/A
9		BARO	Wide Tow	BAROGenerator bracket	1	Acceptable	Wet Stores			N/A

I have imported this into Access, and I am trying to work out how best to set up my relationships, or break down this table into more managable tables.

Any advice greatly appreciated.

Thanks,
Dan

[ranman256]

IF your are tracking ownership,
Table tPersons ,You may want to add field: [PersonID] , the key to the tPersons.
To show each person currently has the items PC abc, PRINTER xyz, etc.

But this method wont work with Qty. (But for cables , qty=3 dont need owners , it does work)

Another table tLease
if you track leasing contracts for printers , pc , etc

FYI.

[DanLarn]

Thanks ranman256, I'm not sure I understand what you've said there.

I am trying to create a database where I can track the location of and quantity of Items, each item has a maanufacturer/supplier, and each item is used in a specific task or 'Role'. My initial thought was to divide the items into two separate tables:

1) Consumables
2) Equipment

Then have a separate table for suppliers, including contact information in there, and another for 'Roles' and finally one for 'Locations'.

Each location is a separate container containing 2 racks (rack 01 and rack 02), and each rack has 5 shelves, would I have to create a database for each container with racks and shelves in separate tables?

[orange]

You might get some ideas from this free data model and the associated business rules.

Do you have a list of business rules regarding your Inventory Control processes?
Creating/designing the database is usually simpler if you have documented the business rules.
The key is to get your tables and relationships designed to support your business rules.
You can then use some sample data to test your model to ensure it does what you require.

This link shows you the steps involved in generic terms.

Good luck.

[ItsMe]

Perhaps you should start by creating a model of your tables.
http://www.youtube.com/watch?v=-fQ-bRllhXc

[DanLarn]

Hello Again,

I am still not really sure what you are all driving at, but I am making headway at least.

I have created separate tables for most of my fields listed above, and then created further tables based on breaking those down even further and established relationships for them all the way back to the top of the tree.

At this point though I am starting to see a few problems arising, I have everything set up as a direct relationship at the moment, but what I really need is more of a parent/child relationship for some of these. Anyone know how I can make those work?