If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Table and Relationship design problem
1. I am attempting to create a db with 62 checklist tables and one table
with the checklist titles that will be used in a form with a combo box and text box. My plan is to use the title of the checklist in the combo box and have the contents of the checklist appear in the text box located on the form. 2. XXXX checklist table design: (primary key) checklistitemid, data type number; checklistid data type number; checklisttitle data type text and lastly checklistitem data type memo 3. Checklists Title table design: (primary key) checklistid data type autonumber; checklisttitle data type text. 4. When I establish the initial relationship, be it one too many, with or without enforced referential integrity, when joining the checklistid field from one of the XXXX checklists table to the checklistid field in the checklist title table the first checklist joins correctly to the checklist title table. The problem arises when I attempt to join the next XXXX checklist table to the checklist title table and open up the checklist title table plus sign. The insert subdatasheet appears and then I have to choose which table to insert. When doing this the checklist title table and the XXXX checklist tables do not “join” correctly. 5. Basically what I’m attempting to accomplish is for each title in the checklist title table, I should be able to click open the plus sign and only see the items that directly related to each title from the applicable XXXX checklist table. 6. I have attempted a linking table and still run into the same problem. Any help with this design and or relationship issue would be appreciated. Regards, Douglas Jones |
#2
|
|||
|
|||
Hi Douglas,
The fact that you have multiple tables with names like XXX_Checklist shows that your database design is faulty. You are in effect storing data in the names of the tables. It also appears that you are redundantly storing the same information in both your Checklists title table and your multiple checklist tables. Instead, use just two related tables, something like this Checklists: ChecklistID (PK) ChecklistTitle (unique index) ChecklistItems: ItemID (PK) ChecklistID (foreign key) ChecklistItem (memo) and use the combobox selection to filter the form's recordsource so it only shows the items in that particular checkbox. (Even simpler, use a subform to display the checklist items.) On Wed, 16 Mar 2005 12:15:05 -0800, "douglas jones" wrote: 1. I am attempting to create a db with 62 checklist tables and one table with the checklist titles that will be used in a form with a combo box and text box. My plan is to use the title of the checklist in the combo box and have the contents of the checklist appear in the text box located on the form. 2. XXXX checklist table design: (primary key) checklistitemid, data type number; checklistid data type number; checklisttitle data type text and lastly checklistitem data type memo 3. Checklists Title table design: (primary key) checklistid data type autonumber; checklisttitle data type text. 4. When I establish the initial relationship, be it one too many, with or without enforced referential integrity, when joining the checklistid field from one of the XXXX checklists table to the checklistid field in the checklist title table the first checklist joins correctly to the checklist title table. The problem arises when I attempt to join the next XXXX checklist table to the checklist title table and open up the checklist title table plus sign. The insert subdatasheet appears and then I have to choose which table to insert. When doing this the checklist title table and the XXXX checklist tables do not join correctly. 5. Basically what Im attempting to accomplish is for each title in the checklist title table, I should be able to click open the plus sign and only see the items that directly related to each title from the applicable XXXX checklist table. 6. I have attempted a linking table and still run into the same problem. Any help with this design and or relationship issue would be appreciated. Regards, Douglas Jones -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. |
#3
|
|||
|
|||
john,
my orginal db design is built in that exact manner and i thought that this proposed design would be better ... my bad thanks doug "John Nurick" wrote: Hi Douglas, The fact that you have multiple tables with names like XXX_Checklist shows that your database design is faulty. You are in effect storing data in the names of the tables. It also appears that you are redundantly storing the same information in both your Checklists title table and your multiple checklist tables. Instead, use just two related tables, something like this Checklists: ChecklistID (PK) ChecklistTitle (unique index) ChecklistItems: ItemID (PK) ChecklistID (foreign key) ChecklistItem (memo) and use the combobox selection to filter the form's recordsource so it only shows the items in that particular checkbox. (Even simpler, use a subform to display the checklist items.) On Wed, 16 Mar 2005 12:15:05 -0800, "douglas jones" wrote: 1. I am attempting to create a db with 62 checklist tables and one table with the checklist titles that will be used in a form with a combo box and text box. My plan is to use the title of the checklist in the combo box and have the contents of the checklist appear in the text box located on the form. 2. XXXX checklist table design: (primary key) checklistitemid, data type number; checklistid data type number; checklisttitle data type text and lastly checklistitem data type memo 3. Checklists Title table design: (primary key) checklistid data type autonumber; checklisttitle data type text. 4. When I establish the initial relationship, be it one too many, with or without enforced referential integrity, when joining the checklistid field from one of the XXXX checklists table to the checklistid field in the checklist title table the first checklist joins correctly to the checklist title table. The problem arises when I attempt to join the next XXXX checklist table to the checklist title table and open up the checklist title table plus sign. The insert subdatasheet appears and then I have to choose which table to insert. When doing this the checklist title table and the XXXX checklist tables do not “join” correctly. 5. Basically what I’m attempting to accomplish is for each title in the checklist title table, I should be able to click open the plus sign and only see the items that directly related to each title from the applicable XXXX checklist table. 6. I have attempted a linking table and still run into the same problem. Any help with this design and or relationship issue would be appreciated. Regards, Douglas Jones -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Seeking some expert advice. | HD87glide | Using Forms | 14 | March 23rd, 2005 10:11 PM |
Relationship design problem with multiple tables | Don | New Users | 3 | November 24th, 2004 06:27 PM |
Table Design & Relationship problem... | Niko | Database Design | 7 | October 23rd, 2004 02:10 PM |
Table Wizard Does Not Set Relationship if Foreign Key and Primary Key Name Do Not Match Exactly in Case. | HDW | Database Design | 3 | October 16th, 2004 03:42 AM |
Complicated Databse w/many relationships | Søren | Database Design | 7 | July 13th, 2004 05:41 AM |