Stumped by "one to many" in query, need some help

[Larry Kahm]

I've got three sample tables:

tblCompanies
CompanyID (PK)
CompanyName
CompanyDesc

tblAddresses
AddressID (PK)
CompanyID (FK)
Address
City
State

tblPhones
PhoneID (PK)
CompanyID (FK)
Phone
Fax

I established the relationships among the three tables. There's a
one-to-many join between tblCompanies and tblAddresses and another between
tblCompanies and tblPhones.

Now, if I build a query with only tblCompanies and tblAddresses, everything
is fine - I can enter a record in the QBE. However, the moment I add
tblPhones, the query is no longer updateable. Thus, if I want to build a
data entry screen based on the three tables in one query, I can't.

I don't want to believe this is the first time (in a very long time) that
I've hit this scenario, but I can't seem to noodle my way past it.

Does anyone have any suggestions - or corrections - to my view of this
problem?

Thanks in advance!

Larry

 

[Jerry Whittle]

First thing is to check the relationships in the Relationships Window. Make
sure that Referiental Integrity is enabled. If there are any data problems
that won't let you, fix them. Then give it a try. Actually I don't think that
you'll get it two work, but RI is a good thing anyway.

If it still won't work, you'll need to use subforms instead. Make the
tblCompanies the main form. Put tblAddresses on one subform and tblPhones on
another subform. If you have the relationships defined with RI enabled, then
the wizard should be able to make the subforms. If you need more real estate
on the screen, consider a tab control with the subforms on different tabs.

 

[Beetle]

As you've discovered, a query involving more than two tables will not
be updateable. The common solution would be to have a main form
with two subforms (perhaps tabbed forms) for data manipulation. Each
form would be based on a separate table/query, linked by CompanyID

 

[Larry Kahm]

Jerry,

I appreciate your prompt reply - somehow my original post is no longer on
the server(!?!).

And your "sig line" just about sums this one up. I >can< get two, but not
the third (and I have Bontrager components on my bicycle).

I'll work it out with subforms for now, using A2000/2003 tabs is just to
ugly to consider for this application.

Thanks!

Larry

 

[aaron_kempf]

yes, you should move to SQL Server-- if you had problems like this
there-- you could just use an 'instead of trigger'.

Otherwise, you're just stuck working with an unpredictable database
that just randomly craps out like this.
Seriously.

Upsize, Upsize, Upsize.

-Aaron