Hi, Until so far I've done all my data in Excel, using it like a sort of database. Now I want to give access a go. Watched some video's and as a newbie I understand basics like one to many and many to many.
What I would like is to create is a product data management hereafter PDM. For that I would like machine numbers existing of (sub)Assemblies with part numbers The problem I encounter that a sort of need to set up a many to many to many system.
The unique part number should be able to be in different sub-assemblies. For instance Machine 1 exists out of two assemblies. Assembly one has part numbers and assembly two exists out of four part numbers and a subassembly existing out 5 part numbers.
Machine 2 has 3 assemblies one of them the same as machine 1, the other two different.
My question. Is the desired possible in access? I've been trying a lot of relations, genealogy, etc. but so far I didnt figure it out. Maybe Access does not allow the desired and am I wasting my time.
Hope somebody can answer
Kevin Yip
@Reply 3 years ago
To set up such a relationship, your tables need to have the right "primary keys" in order to form those relationships. Suppose your products consist of different styles, and each style consists of different colors, and each color has different sizes. To form such a one-to-many-to-many relationship, your tables need to look something like below:
Color table:
StyleID (PK) ColorID (PK) ColorName
1 1 Black
1 2 Ivory
1 3 Grey
2 1 Blue
2 2 Orange
3 1 Purple
3 2 Green
Size table:
StyleID (PK) ColorID (PK) SizeID (PK) SizeName
1 1 1 Small
1 1 2 Medium
1 1 3 Large
1 2 1 Medium
1 3 1 Medium
2 1 1 Petite Small
2 1 2 Petite Medium
2 2 1 Petite Large
2 2 2 Petite XLarge
3 1 1 1X
3 1 2 2X
3 1 3 3X
Columns indicated with "(PK)" means they are primary key fields. That means they must be unique in the table. For instance, in the style table, only one style has the StyleID of 1, because Style ID is a PK. Access would not allow another row to have StyleID 1 -- the same way no two people are allowed to have the same social security number or driver's license number. In the color table, only one row has the unique combination of Style ID 1 and Color ID 2, because the PK is the StyleID/ColorID combo. And so on.
What the data above tell us is:
- Style S1000 has 3 colors: black, ivory, grey. Black comes in small, med, large. Ivory and grey only come in medium.
- Style S2345 has 2 colors: blue and orange. Blue comes in petite small and med. Orange comes in petite large and xlarge.
- Style S6789 has 2 colors, purple and green. Purple comes in sizes 1X, 2X, 3X. Green's sizes are not specified.
This is basically what "normalized tables" with one-to-many-to-many relationship look like. It is a completely different scheme compared to Excel, because you would never enter data this way in Excel. But in Access, you have to, in order to have such as setup.
Scott Axton
@Reply 3 years ago
The example Kevin provided is great. A couple more examples exist.
In the Chef's Kitchen Helper video. Many ingredients can go into making different recipes.
In the Nested Subforms video Richard show and example of schools. From department to courses to classes to students.
So the short answer is yes, Access can do it. This is a pretty complex task that requires a good bit as far as the learning curve goes.
Sorry, only students may add comments.
Click here for more
information on how you can set up an account.
If you are a Visitor, go ahead and post your reply as a
new comment, and we'll move it here for you
once it's approved. Be sure to use the same name and email address.
This thread is now CLOSED. If you wish to comment, start a NEW discussion in
Visitor Forum.
