Access for Manufacturing applications

[blacktusk]

Hey guys,

New to the site and I was hoping someone here could answer a couple questoins.

We are a small manufacturing company that builds performance BMW parts. We are in need of tracking inventory on a completed level basis. By this I mean when we have one Kit or system done and boxed up to go out, we need the inventory system to debit all the parts inventory that went into making that finished good.

Some kits contain three pieces, other contain 20 different pieces. And we are getting to the point where we are too big to just look and see what needs to be reordered and obviously that isn't a good practice anyway.

We don't have the money right now to spend 50,000 on a pro inventory system and was wondering if access will suffice for us.

I have been trying the last few days to set it up myself but i can't figure out the relations and how to tie them all in. A little help maybe a template of some sort would be greatly appreciated. ANY guidance whatsoever would also help.

Thanks,
-Brody-

[MMcCarthy]

Hi Brody

Have a look at this tutorial Normalisation and Table structures.

I think your structure should be something like the following:

tblKit
KitID
KitName

tblPartPerKit (tblKit and tblPart have a many to many relationship so a Join table is required)
KitID
PartID

tblPart
PartID
PartName

tblOrder
OrderID
OrderDate

tblOrderDetail
OrderDetailID
OrderID
KitID
Quantity

tblPartPerKit would store the partID corresponding to every kitID e.g.

KitID   PartID
1     3
1     6
1     8
2     5
2     6
 

PartID 6 is in both KitID1 and 2.

Get this sorted first and you will find you can create queries to work out the rest.

Mary

[blacktusk]

Thanks Mary, I will give that a shot and see what happens.