Help needed with tables & relationships (1 Viewer)

Morning All,

I am relatively new to Access, I have built several stand alone systems that work reasonable well for what they are however I have a problem and don't know what to use as key words to search the forum.

I would like to set up a 2 tables where by when one table has been filled with information, it can then populate a pre given cell with information from the other table. IE if I have a machine (that we are building) and all the relevant information for that machine build in one table and I want to assign a serial number for a part, which we track in another table, how do I link the two so that when we assign the part (and serial number) to a particular machine it populates the machine table as well with the serial number?

I would like to add that the machine has a unique serial number for the completed machine and there are several parts with serial numbers to assign to the completed machine as well.

Hope this makes sense and someone can point me in the right direction as I have a lot of these machines, with several serial numbers, that need this solution.

Thank you
Chris

You have identified two entities, Machines and Parts and recognize the need to have a table for each. Experienced developers will normally prefix their table names with 'tbl' to identify them as tables ie tblMachines, tblParts.

Every table should have its own unique identifier which is called the Primary Index (also known as Primary Key or PK for short) eg PartID and MachineID. To ensure these are unique and automatically added by Access make the field type for both Autonumber. You could use the serial number as a primary key but I would store the serial number in a separate field with a unique index on it. I do this for a number of reasons but principally for faster lookup of numbers rather than strings.

Normally, to relate tables, one table will contain the PK of another table. This is called a Foreign key or FK for short.

However, as you have multiple parts for each machine, you need a third table, say tblMachineParts. This table will have at least 3 fields, MachinePartID (autonumber), MachineID and PartID. Maybe there would be another field for the number of each respective part in a machine.

For data input and disply, you would have at least a form, called say frmMachines bound to tblParts. This form would have a subform bound to tblMachine parts. Set the relationship (Master/child fields) between the 2 forms on MachineID. The subform should be set as continuous and use a combo to select the particular part being added. The combo will be bound to tblParts but the value of PartID will be stored with MachineID in your join table.

I'm sure others will add to this or give a different slant but this should be enough to get you well and truly started.

But one thing in particular, do not be tempted to use a multivalued field. It will cause you endless grief with further development.

As to the question of "what to look up for some reading"? Look up "Junction Tables" and of course, if you have not done so before this, "Database Normalization."

Thanks Cronk, I will set this up and see how I go. Will let you know once i have done it.

Thanks again for the assistance

Thanks The_Doc_Man, will do.

And specifically with regard to your question:

how do I link the two so that when we assign the part (and serial number) to a particular machine it populates the machine table as well with the serial number?

Click to expand...

Well if you follow Cronk's advice then the "parent" table of machines doesn't actually need to be "populated" with anything new concerning a new part for that machine. Just the "child" table of parts needs a record containing the "parent" table's ID or serial number for a specific machine.