Linked tables & form

[Damo10]

Hi,

I have been tasked with creating a seal strip database, I am stuck on the best way of creating the tables & form and any help would be much appreciated.

What I have so far is a table with all the different seal types and descriptions & part numbers, another with the machine number and quantities of each seal type.

What I am stuck on is how to link them together and onto 1 form so that when a machine number is entered it retrieves the number of each type of seal but I also need to enter a quantity of seals replaced, number of failed seals for each seal type and then save this into a table along with the date of the seal strip.

Regards,

 

[Micron]

Your seal table needs a field that is common with the machine table. Some would do this with a SealID autonumber field in seal table and that number in the SealID field of machine table. Autonumbers don't make great primary/foreign key partners IMHO, so I would use the part# in seal table as the foreign key in machine table. Either way, the objective is to link the machine info with the seal part#, description, cost, etc. in the seal table. I say this with the notion that you don't need a master parts catalog. Don't go too far in adding fields to your tables lest your db becomes less normalized. One way of doing this would be to put suppliers info into the seal table. That would be a mistake. The supplier primary key would be a foreign key in your seal table.

 

[HiTechCoach]

WARNING: Inventory control is one of the most difficult things to program with any database/programming language.

Sounds like what you needs is a multiple warehouse inventory control system. In your case each "warehouse" is a machine.

You should use a transactions to add and remove items from inventory. To get the current quantity on hand you run a query to sum up the transactions.

This may help: Inventory Control: Quantity on Hand

 

[Micron]

Well, I took this to be a BOM or bill of materials project requiring the association between a machine and a list of parts (in this case, seals) that are on it. Much like your local auto dealership has a BOM for your car. Damo10, what is the nature of the database you are trying to create? Is it inventory?