Hello all,
I have a scenario where a many to many table is needed but I can't it to work in the way that I need. I currently have to add 10 columns to my main table then use a union query to stack them with the rows primary key to recreate the many to many table I want to use. The 10 columns pull accessories from another table which have different categories. The 10 columns I add to the main table are for each category which pull all the available accessories for that category via a query.
When I add a new primary record with all it's info on the mainform, I want to use a subform within the main form with 10 combo boxes for the user to select the desired accessory for each category and have it add all 10 of those selections with the primary records key as 10 individual rows to the many to many table. I feel like I'm overlooking something obvious but it's just not coming to me. The 10 extra columns in main table work fine for what I'm doing but I'd much rather setup the relationship properly with a many to many table. If I'm mistaken in this thinking, please let me know and thanks for any help/insight you can provide.
Here is a very simplified version of the data scenario I'm trying to accomplish. I'm only using 3 categories/columns for these examples:
Door_Locations - Main form for adding new record
Detail_List - The table that Door_Locations pulls from to get the accessories by the 10 categories I mentioned
This is what the Many To Many Table would look like
This is how I have to setup the main table then use a union query to recreate the Many To Many table I want
Here is how I want to use the form with above data and the relationship setup between the tables.
Thanks again for any help you can provide.
I have a scenario where a many to many table is needed but I can't it to work in the way that I need. I currently have to add 10 columns to my main table then use a union query to stack them with the rows primary key to recreate the many to many table I want to use. The 10 columns pull accessories from another table which have different categories. The 10 columns I add to the main table are for each category which pull all the available accessories for that category via a query.
When I add a new primary record with all it's info on the mainform, I want to use a subform within the main form with 10 combo boxes for the user to select the desired accessory for each category and have it add all 10 of those selections with the primary records key as 10 individual rows to the many to many table. I feel like I'm overlooking something obvious but it's just not coming to me. The 10 extra columns in main table work fine for what I'm doing but I'd much rather setup the relationship properly with a many to many table. If I'm mistaken in this thinking, please let me know and thanks for any help/insight you can provide.
Here is a very simplified version of the data scenario I'm trying to accomplish. I'm only using 3 categories/columns for these examples:
Door_Locations - Main form for adding new record
PK_Door | Door Identifier | Room | Door Part No | X Cord | Y Cord | Closer Required | Holdback Required | Lock Required |
1 | 1-1E | Room 1 | 62B-2 | 156.25 | 46.125 | YES | YES | YES |
2 | 1-2C | Room 1 | 45A-6 | 654 | 22.625 | NO | YES | NO |
3 | 3-1B | Room 3 | 92M-7 | 426.185 | -146 | YES | NO | YES |
4 | 2-1C | Room 2 | 84A-1 | 23.625 | -82.75 | YES | NO | NO |
Detail_List - The table that Door_Locations pulls from to get the accessories by the 10 categories I mentioned
PK_Detail | Detail | Detail For | Description |
1 | N/A | Not Used | N/A |
2 | 1A | CloserDetail | Door Closer 1 |
3 | 1B | CloserDetail | Door Closer 2 |
4 | 1C | CloserDetail | Door Closer 3 |
5 | 2A | HoldbackDetail | Holdback 1 |
6 | 2B | HoldbackDetail | Holdback 2 |
7 | 2C | HoldbackDetail | Holdback 3 |
8 | 3A | LockDetail | Lock 1 |
9 | 3B | LockDetail | Lock 2 |
10 | 3C | LockDetail | Lock 3 |
11 | 4A | InstallationDetail | Install 1 |
12 | 4B | InstallationDetail | Install 2 |
13 | 4C | InstallationDetail | Install 3 |
This is what the Many To Many Table would look like
PK_DetailRequired | FK_Door | FK_Detail |
1 | 1 | 4 |
2 | 1 | 5 |
3 | 1 | 10 |
4 | 1 | 11 |
5 | 2 | 1 |
6 | 2 | 6 |
7 | 2 | 1 |
8 | 2 | 12 |
9 | 3 | 2 |
10 | 3 | 1 |
11 | 3 | 9 |
12 | 3 | 13 |
13 | 4 | 4 |
14 | 4 | 1 |
15 | 4 | 1 |
16 | 4 | 12 |
This is how I have to setup the main table then use a union query to recreate the Many To Many table I want
PK_Door | Door Identifier | Room | Door Part No | X Cord | Y Cord | Closer Required | Closer Detail | Holdback Required | Holdback Detail | Lock Required | Lock Detail | Installation Detail |
1 | 1-1E | Room 1 | 62B-2 | 156.25 | 46.125 | YES | 1C | YES | 2A | YES | 3C | 4A |
2 | 1-2C | Room 1 | 45A-6 | 654 | 22.625 | NO | N/A | YES | 2B | NO | N/A | 4B |
3 | 3-1B | Room 3 | 92M-7 | 426.185 | -146 | YES | 1A | NO | N/A | YES | 3B | 4C |
4 | 2-1C | Room 2 | 84A-1 | 23.625 | -82.75 | YES | 1C | NO | N/A | NO | N/A | 4B |
Here is how I want to use the form with above data and the relationship setup between the tables.
Thanks again for any help you can provide.