Joining 3 tables with no repeating for a report

  • Thread starter Thread starter Carlene
  • Start date Start date
C

Carlene

I have four tables - Practice, Practice_Doctor (a table joining
practice and doctor IDs so one practice can be long to many doctors
and vise versa), Doctors, and Products, and I want to create a report
for each practice that shows Practice - all the doctors related to the
practice, and then all the products the practice has as
well...products and doctors are unrelated.

When I try doing a join on the tables and then Group them by Practice,
then Doctor, then product - i get something like this

Practice...
Doctor 1
Product 1
Product 2
Product 3
Doctor 2
Product 1
Product 2
Product 3

which i dont want - i want it to look like this

Practice...
Doctor 1
Doctor 2
Product 1
Product 2
Product 3

I actually dont care how it is displayed as long as i can have
everything on one page and i guess joining the tables wont do that...
I would settle for creating a report that has the practice and the
doctor and then i embedd a list box that shows all products related to
that practice just so i have everything on one page. PLEASE HELP

Thanks in advance!!!
 
It looks like you don't want to group by doctor at all,
just practice with a list of the doctors in the practice.
Try removing the doctor group.
 
When I remove that and put everything in the details section it will
still repeat the doctor/product - for instance is there is 1 doctor
and three practices - it will list the doctors name three times next
to the three products. Any other suggestions?
 
Back
Top