Which of these strange things is best?

[Julia Baresch]

Hi everyone,

My database has 3 data tables with chained one-to-many relationships
i.e.

Table1 1-->Many ->Table2 1-->Many ->Table3

I added a fourth table to hold supplemental data that also relates to
Table2. It also has a one-to-many relationship with Table2, so now
Table2 has two child tables

Table2 1-->Many ->Table3 and
\ 1-->Many ->Table4

And I designed subform4 that opens with a button from subform2 for
display and entry of the supplemental data, with code that
automatically inserts the control numbers in the appropriate fields of
the subform. This works fine for our purposes.

When I wanted to query this data, I put all 4 tables in the query and
it wouldn't run. I found this was because I had both Table3 and
Table4 in it, and it didn't like the 2 tables attached to Table2. It
gave a message about ambiguous outer joins. Then I realized I didn't
need both of those tables in the query and took one out, and the query
ran perfectly. However, I would like to be able to use both tables in
one query if necessary.

I tried adding the data fields from Table4 to (a copy) of Table2, but
it behaves oddly when I add new records in subform4. The new records
don't contain the automatically populated control number and therefore
don't show next time the form is opened. Also using this method would
leave most of the fields in the record of Table2 blank. It would make
one record for most of the Table2 data, and then if data is added for
the same record in subform4 at a later date, it makes a new record to
hold data that belongs to the earlier record. This seems wrong to me.
Maybe I can fix it so subform4 shows the extra fields in the same
record.

It would be possible for Table3 and Table4 to have a many-to-many
relationship since they both link to the same control in Table2. I
did some research here and found directions on creating an
intermediary table to hold one-to-many in each direction for the two
tables.

Which of these options is better? To leave it the way it is and use
subqueries if the need arises? To make it so the supplemental data is
stored in the same table as the master data but shown in the subform,
if that's possible? Or to use an intermediary table between Table3
and Table4?

Any and all feedback is welcome. Thank you. :-)

[Baz]

Hello Julia,

There is nothing wrong in principle with your data structures: you got the
error message not because of the relationships between the tables, but
because, as the message said, your query contained ambiguous outer joins!
There is usually a way around this, but in your case it is impossible to say
what it might be unless you post the SQL for the query!

Baz

"Julia Baresch" <jb******@oldrepublic.com> wrote in message
news:50**************************@posting.google.c om...

Hi everyone,

My database has 3 data tables with chained one-to-many relationships
i.e.

Table1 1-->Many ->Table2 1-->Many ->Table3

I added a fourth table to hold supplemental data that also relates to
Table2. It also has a one-to-many relationship with Table2, so now
Table2 has two child tables

Table2 1-->Many ->Table3 and
\ 1-->Many ->Table4

And I designed subform4 that opens with a button from subform2 for
display and entry of the supplemental data, with code that
automatically inserts the control numbers in the appropriate fields of
the subform. This works fine for our purposes.

When I wanted to query this data, I put all 4 tables in the query and
it wouldn't run. I found this was because I had both Table3 and
Table4 in it, and it didn't like the 2 tables attached to Table2. It
gave a message about ambiguous outer joins. Then I realized I didn't
need both of those tables in the query and took one out, and the query
ran perfectly. However, I would like to be able to use both tables in
one query if necessary.

I tried adding the data fields from Table4 to (a copy) of Table2, but
it behaves oddly when I add new records in subform4. The new records
don't contain the automatically populated control number and therefore
don't show next time the form is opened. Also using this method would
leave most of the fields in the record of Table2 blank. It would make
one record for most of the Table2 data, and then if data is added for
the same record in subform4 at a later date, it makes a new record to
hold data that belongs to the earlier record. This seems wrong to me.
Maybe I can fix it so subform4 shows the extra fields in the same
record.

It would be possible for Table3 and Table4 to have a many-to-many
relationship since they both link to the same control in Table2. I
did some research here and found directions on creating an
intermediary table to hold one-to-many in each direction for the two
tables.

Which of these options is better? To leave it the way it is and use
subqueries if the need arises? To make it so the supplemental data is
stored in the same table as the master data but shown in the subform,
if that's possible? Or to use an intermediary table between Table3
and Table4?

Any and all feedback is welcome. Thank you. :-)

[Julia Baresch]

Baz,

Thanks for your feedback! It's good to know my data structure
instincts weren't wrong. :-)

I tried to re-create the query situation, but now even with both
tables 3 and 4 the query works fine. I suppose I missed something the
first time. I'll keep watching for it and maybe if it happens again we
can figure it out more easily.

Thanks again for your help, and thanks to Tom also for your e-mail.
:-)

Julia

"Baz" <bc**@clara.co.uk> wrote in message news:<10****************@ersa.uk.clara.net>...

Hello Julia,

There is nothing wrong in principle with your data structures: you got the
error message not because of the relationships between the tables, but
because, as the message said, your query contained ambiguous outer joins!
There is usually a way around this, but in your case it is impossible to say
what it might be unless you post the SQL for the query!

Baz