Inventory Database

[Troy Ward]

Ok guys heres the deal. I am working on designing an
inventory database. What I would like to do is create a
database of all of the equipment that I am responsible
for. These items are made up of a large number of
components all of which must be tracked. Most of these
items are then signed over to other people from me. I
plan on making a master table that includes all possible
items (end item and components). I would like to find a
way so that when add a new end item, it automaticlly
creates new records for the necisarry components. Any
ideas on how to design the tables exactly to do this. I
know this is probably a little confusing but if you think
you can help, email or post and I will go into great
detail.

Thanks,

Troy Ward

 

[Adrian Jansen]

Ok guys heres the deal. I am working on designing an
inventory database. What I would like to do is create a
database of all of the equipment that I am responsible
for. These items are made up of a large number of
components all of which must be tracked. Most of these
items are then signed over to other people from me. I
plan on making a master table that includes all possible
items (end item and components). I would like to find a
way so that when add a new end item, it automaticlly
creates new records for the necisarry components. Any
ideas on how to design the tables exactly to do this. I
know this is probably a little confusing but if you think
you can help, email or post and I will go into great
detail.

Thanks,

Troy Ward

You will have to specify how you want each item broken down into
components, nothing automatic will do that. Suppose you have a
computer, its components may well be case, power supply, keyboard,
monitor, motherboard, etc. Do yout want to break the motherboard down
into parts too ? And those parts down into their parts... and so on
down to atoms ?

But once you decide that, its relatively easy to put all the component
parts and their assemblies into a table, and organise the relations
between then so you can build up an assembly from its components, or
break it down.

There is some good stuff at http://www.mvps.org/access on inventory
control. Check out also articles by Joe Celko on adjacency lists and trees.

Consider the following as a raw start:

tblItemMaster
ItemID primary key
Part number
Description
etc

tblAssembly
AssemblyID - foreign key to tblItemMaster for the assembly
ComponentID - foreign key to tblItemMaster for the component

tblIssuedTo
ItemID - FK to tblItemMaster
PersonID - FK to tblPersons
QuantityIssued

Then you can write queries to extract higher and lower level assemblies
and their components.
And also queries to find out who has what parts at any time.

--
Regards,

Adrian Jansen adrianjansen at internode dot on dot net
Design Engineer J & K Micro Systems
Microcomputer solutions for industrial control
Note reply address is invalid, convert address above to machine form.