Table and Relationship design problem

[douglas jones]

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]

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.)

 

[douglas jones]

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