Part Number Fields 1-10 is a repeating field? Or ten separate fields?
The Databases are related with what fields?
The suggested relationship would be with an auto-entered serial number.About planning a databaseA well-designed database promotes consistent data entry and retrieval, and reduces the existence of duplicate data among the database tables. Relational database tables work together to ensure that the correct data is available when you need it. It’s a good idea to plan a database on paper first.Follow these general steps to plan a database:
Relational Database Design 101 (part 1 of 3): Designing a Flat File Database
Relational Database Design 101 (part 2 of 3)
Relational Database Design 101 (part 3 of 3)
They are 10 seperate fields each with an individual part number and a corresponding portal which should link to an identical part number in the seperate database. I'm afraid the serial number is not an option at this point.
You should transition to internally generated serial numbera as soon as possible and yes, this could take time and effort to do. It isn't part of your current problem, however.
What you describe should work, but it's not the ideal design. Restructuring so that you have only one part number field to each record and thus only one relationship instead of 10 greatly simplifies your design in many ways.
Off hand, I suspect that your current set of 10 relationships aren't set up exactly the same way, but I'm guessing based on very little info.
You are correct I have 10 relationships to 10 seperate tables. I recognize this is not ideal however this is somewhat of a workaround.
10 Separate tables or 10 occurrences of the same table?
If you have 10 separate tables, that sounds like a recipe for disaster.