Problems with simple linking of tables I think --fidsgal

  • Thread starter Thread starter fidsgal
  • Start date Start date
F

fidsgal

I have 3 tables that have the relationships as shown below. The 1st
table is the Main form. The 2nd table is a subform of Table 1, and
likewise table 3 is a sub of 2.

COI Table (#1):
COI ID (Primary Key)
field 2
field 3, etc.

Sub COI Table (#2):
COI ID (Primary Key)
Sub COI ID (Primary Key)
field 3
field 4, etc.

Sub Sub COI Table (#3):
Sub COI ID (primary key)
sub sub COI ID (Primary key)
field 3
field 4, etc.

GOAL: [COI ID] in #2 should display the same as in #1 AND [Sub COI
ID] in #3 should display the same as in #2.

Thank you for helping me!
--Amy
 
Fidsgal, in Table#2 Sub COI ID should be the primary key [preferably
AutoNumber], while COI ID should be a foreign key [Number/Long Integer if
the corresponding field in Table#1 is AutoNumber]. Link Tables#1 and 2
One-to-Many on COI ID, with Table#1 on the "One" side.

Similarly, in Table#3 Sub Sub COI ID should be the primary key [preferably
AutoNumber], while Sub COI ID should be a foreign key [Number/Long Integer
if Sub COI ID in Table#2 is AutoNumber]. Link Tables#2 and 3 One-to-Many on
Sub COI ID, with Table#2 on the "One" side.

That takes care of your table relationships. Now let's talk forms and
subforms. Use the Form Wizard to create a form. Start with Table#1 and
include all the fields, then add Table#2 and include all the fields EXCEPT
the foreign key, then add Table#3 and include all the fields EXCEPT the
foreign key. Proceed to the next Wizard screen and you will see that the
Wizard proposes to create a One-to-Many-to-Many form (smart Wizard!). Make
your way through the Wizard and at the end you will achieve your goal.
 
Back
Top