Linking Tables

[connerlowen]

Hi,

I need help with some tables. I have a master table that has a part number and steps, and within each step there is a sub part number. I also have a table for each step and within those tables there are the sub part numbers. On the master table in the step field I want to be able to select from my step tables, and in the sub part number field I want to reference the step field and select a part number from within the step table. I am not sure how to get this done, or if it is even possible. Any help at all would be greatly appreciated.

Thanks

 

[CJ_London]

can you provide some information about your table structures and some sample data with the required result - I'm not clear what you are asking for

 

[connerlowen]

I have a table called "FinishedProductT". This table will store a part number for a ready to sell product which I call the Master Part Number. Within this table there are steps to get from raw material to finished product. Some have more steps than others. I have 7 different tables for the internal processes which are the steps. Within each step there are different part numbers corresponding to what the workers need to make, I call these the sub part numbers. For example:
Master part number is 002-0585-010-0000
the first step for this part number is SwissScrew
the sub part number within the swissscrew step is 002-0585-000-0000
the next step is Brazing
the sub part number within the Brazing step is 002-0585-010-0000 IP-BRZ
the part is then sent out of the company for the last step.

Currently the employees must raise a work order for each sub part number for each step and check the items in and out of stock after each step. The database is going to raise one work order under the master part number which includes all steps as well as sub part numbers.

within the table "FinishedProductT" I want to be able to choose the steps from the 7 step tables and then the next field is the sub part number within the assigned step. I would like for that field to look at the step field and then provide a drop down menu for the employee to choose the sub part number.

Hopefully this makes sense. Thank you again for your help.

 

[CJ_London]

descriptions are all very well, but as requested I need to see your table structures and some sample data from each of those tables plus an example of what you want as a final result. I am otherwise unable to help

Use screenshots if necessary

 

[connerlowen]

I have attached the three tables referenced in the descriptions. within the Brazing and ScrewMachine tables there are many more fields, but I really only need the part number to be linked and the rest of the fields can be called automatically.

 

[connerlowen]

Final Result: I want an employee to be able to log in to my database and click a button that takes him to a form where he can create a new job. I want him to be able to choose from a drop down menu any Master Part Number he wants, and enter a quantity, date, and some other fields which will create a new record in a new table called "InProgressJobsT". The data in the tables "FinishedProductT", and the 7 steps will remain constant and will need to be called when a new job is created. this will all be done through a form, however I am assuming I need to set up the tables this way so that the form will work correctly.

 

[CJ_London]

Your data is not normalised so any solution is going to be a) more complex and b) difficult to maintain.

Assuming sub part numbers can be used in many master part numbers and the same for the other link you need two extra linking tables

the first one would be to link sub parts to main parts

tblMastSubLink
MastSubPK autonumber
MasterPartNo text
SubPartNo text

tblSubSubLink
SubSubPK autonumber
SubPartNo text
SubSubPartNo text

because your data is not normalised, each of step1partno, step2partno etc in the finishedproduct table will need to be linked to tblMastSubLink almost certainly with left joins creating a very inefficient query and potentially slow query.

 

[connerlowen]

what do you mean by normalized? I am new to Access.

 

[CJ_London]

just realised with your present structure you don't need the first linking table, you would link each of the steppartnos to the relevant record in the subpartno record.

Normalisation is about organising tables in a way that you do not have duplicate or redundant information. Google 'normalisation' and you will find hundreds of links on the subject

In your case you have redundant information in that you have processes 'going across' (aka Excel) - so if you don't use all the processes, unused ones remain blank and if you have more processes you will have to change the table design and modify all resulting queries forms and reports.

Your code will be much more complex because you need to 'cater' for blank fields and in your case, you will end up with wider and wider query results because all the data will need to be on one line - and repeated over many lines.

I don't know your process but would suggest your finished product table needs to be split into two tables. The first containing data specific to the finished product (like masterpartno) and the second containing one record for each process - looks lke it would be stepno, partno and hold