Subdatasheet help

[Jennifer]

Hi,
I am creating my first database and have struggled with
this problem for awhile now, hoping someone can set me
straight.

I have set up differant tables:
Consultant info (ID-Primary, FName, LName, etc)
Doctors Office (ID-Primary, address, FName, LName,
Consultant ID, etc)

I have set a relationship between the consultant ID field
in each table. But when I look at the table I want to
press the little plus (+) sign in the consultant info
table and see all her clients(doctors offices) This should
go vise versa in the Doctors Office table I should (+) and
see what consultant they use and there info.

Hope this is making sense. I have myself so confused.
Thank you for any insight you might be able to give.
Jennifer

 

[Pavel Romashkin]

What you want to do is a many-to-many relationship. This is accomplished
by setting up a junction table that will consist of DoctorID and
ConsultantID. Then, link the DoctorsID from the Doctors table to the
DoctorsID in junction table, and do the same for Consultants. For the
junction tabk,e set primary key to be the composite of the two foreign
keys. You can do this by highlighting both IDs in design view and
selecting Edit -> Primary key from the menu.
The way you have it set up now is, each Doctor can have 1 consultant
(because there is 1 place to store ConsultantID per Doctors record). It
is a 1-to-many relationship. It is not what you want.

Good luck,
Pavel