Linking tables

[MJD500]

Hi:
I have 2 tables.

Resolver Group table Client table
1st contact name Address
2nd contact name Phone number
3rd contact name Pager number
Resolver group name Name

The end result of the report is to list the address and phone of each of the contacts for each resolver group. If I do an equal join with the 3 contact name fields from the resolver group table to the name field of the client table, I get only the records which all 3 contact names are the same and the address, phone number, etc.. is displayed. If I do a left outer join with the 3 contact name fields, I get all records, but the address and phone number fields are blank. If I do a right outer join, I get the same results as the = join.

How can I list all 3 contact names and display their address, phone #, etc...

Can anyone help me with this please?

Thank you very much.

MJ500

 

[balves]

Your major problem is bad db design. The Resolver_Group table should only have a group name and a contact name. If you need another field to designate the primary contact or type of contact, fine. But not 3 names in one record.

You can try adding the Resolver_Group table to the report 3 times. Then you'll have Resolver_Group, Resolver_Group_1 and Resolver_Group_2. You can equi-join these 3 tables. Then left join a name in each table to the Contact table.

 

[MJD500]

Hi:

Thanks for your reply. I did try what you said and I was able to get all records, which is good BUT I'm not sure how to display the phone #, address for each contact. This is what I've done.
GH1 is the group name and then
GH2 RG.1st contact name address phone #
GH3 RG1.2nd contact name address phone #
GH4 RG2. 3rd contact name address phone #

The address and phone is the same for all contacts in the report where in reality, the address and phone # is supposed to be different. I had assumed that by picking the address field from the client table and putting it in the group, that it show the address of the contact name in the group??? What am I doing wrong? Again, thank you for your help

Jocelyne

 

[balves]

Okay, I had it backwards.

Add the Resolver_Group table once and the Client table 3 times so that you get Client, Client_1, Client_2. Then left join the Resolver_Group.1st_contact_name to the Client.Name, the Resolver_Group.2nd_contact_name to the Client_1.Name, and the Resolver_Group.3rd_contact_name to the Client_2.Name. So you have separate names/address that match up to each name in 1 Resolver_Group record.

 

[MJD500]

Well balves. It worked beautifully!! Thank you so much.