Information from many to many table relationship

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have two tables, tblclients (PK clientID - autonumber) and tbldoctors
(DoctorID - autonumber)joined in a many to many relationship by a junction
table, tbljuctiondoctorsandclients (PK's ClientID and DoctorID, both number
long integer)

My eventual goal is to print a report that will show both dr and client
information.
For now, my client table as a field "Doctor" that looks up it's values from
the Dr. table.
I know this report is supposed to be based on a query, but because of the
junction table, I'm not sure how to extract this information. When I put all
the tables there, I don't get any results. (SQL below)

SELECT tblclients.First, tblclients.Last, tbldoctors.Name
FROM tbldoctors RIGHT JOIN (tblclients LEFT JOIN
tblJunctionDoctorsandClients ON tblclients.ClientID =
tblJunctionDoctorsandClients.ClientID) ON tbldoctors.DoctorID =
tblJunctionDoctorsandClients.DoctorID;

Not sure what I need to do, because I need to replicate these results in
other queries, such as client info and pharmacies, hospitals, etc.

Determined,

John.
 
"For now, my client table as a field "Doctor"... You should not have a
Doctor field in the client table since that is what you are using the
junction table for.
 
Back
Top