CODEWITHSUNDEEP
databasems-accessdatabase-designrelationship
Kevin K
Kevin K

Reputation: 5

MS Access Relationships and Linking Data

I have several raw materials, stored by their part numbers (unique) in a table (other fields include description, units, quantity, date bought etc.) An assembly (stored in a separate table) requires several of these raw materials in order to construct it. These raw materials are not exclusive to any certain assembly - there could be multiple different assemblies which use the same raw material.

I have thought of creating a separate table where there is a field for the assembly and one for raw material - allowing them to be linked. However, this means that for one assembly that has for example three raw materials, there would be three records created with the assembly field repeated (not ideal, as it would result in a very large database).

My limited understanding of relationships tells me that it's not possible to link these without a common primary key, and even if there was one, I can't see how I would link multiple raw materials to one assembly. Given that users would over time, add new raw materials and new assemblies to the two tables, this adds complexity that I, with my limited understanding of MS Access, am not sure can be accomplished.

Maybe there is a better way of storing the data or maybe SQL in MS Access can accomplish this? But I am even more clueless with SQL than Access.

Any assistance and advice is greatly appreciated.

Upvotes: 0

Views: 92

Answers (1)

Erik A
Erik A

Reputation: 32682

This is very possible, and something that has been done lots of times before.

Imagine your raw materials table looks something like this:

+---+--------+
| 1 | Metal  |
+---+--------+
| 2 | Wood   |
+---+--------+
| 3 | Rubber |
+---+--------+

And your assemblies look something like this:

+---+-------+
| 1 | Bike  |
+---+-------+
| 2 | Table |
+---+-------+

Then, to create a many-to-many relationship, you need a third table that combines the two (columns AssemblyID, MaterialID):

+---+---+
| 1 | 1 |
+---+---+
| 1 | 3 |
+---+---+
| 2 | 2 |
+---+---+

As you can see, the bike requires rubber and metal, and the table only requires wood.

There are a lot of more detailed guides on many-to-many relationships in Access. But the core concept is you need an extra table to link the two together.

Upvotes: 1

Related Questions