Query Me This -- two references to another tables field

[CS]

I have:
tblContacts -- primary key is ContactID, autonumber -- general contact info
tblReferrals -- primary key is RContactID, number, no duplicates -- table
contains info
about who referred who.

In tblReferrals, the ContactID from tblContacts is referenced twice -- once
as the Primary Key RContactID which says who is being referred, again under
ReferredByContactID (foreign key number field) which says
who referred them.

I want to query these tables to print a cross-reference report of who
referred
who, and show the concatenated full names of both the person being referred
and the person referring them.

My problem: If I join the tables in the query by
tbleContacts.ContactID<>tblReferrals.RContactID, I can concatenate the
name of the person being referred, but not the name of the person referring
them. If I join by ContactID<>ReferredByContactID, same problem in
reverse -- name of referrer but not the person referred.

How can I query to pull from the tblContacts the full concatenated name of
both the person being referred and the person who is referring them?

Thanks in advance for any help.
Carol

 

[Steve Schapel]

Carol,

Add the tblContacts table twice to your query. One copy will be joined
to the RContactID and the other to the ReferredByContactID.

Having said that, it seems to me that your design here is more
complicated than it needs to be. Each contact can only be referred by
one other contact, right? So why not just put a ReferredBy field in the
tblContacts table, and remove the tbleReferrals table altogether?

 

[CS]

Thank you, Steve, for your quick response -- That worked perfectly. I am
still a newbie, and am most appreciative of your, and all the MVPs, help.

Regarding my table design. I wrestled with this one. I currently have 38
fields in my tblContacts already -- all basic info that nearly every contact
has (name, address, email, etc.) -- some of these contacts have people who
refer them, while others are self-referred or find the organization through
its website. They are also referred for different things (events,
membership, personal counseling work, etc. -- some of these referrals
qualify them for certain discounts, others do not), or they may be referred
by someone who is not already in the tblContacts, so I thought it better to
capture this data (which is not included for every contact) in a separate
table, rather than cluttering up the basic Contact table. Is my logic
screwy on that?

Thanks again, and I look forward to any advice you have for me about my
design.
Carol

 

[Steve Schapel]

Carol,

I agree that it is often a moot point whether to move to a 1:1
'sub-typing' table. One of the things to consider is the number of
"sub-types" involved, and the proportion of records which would
otherwise end up with blanks in a significant number of fields.
However, the total number of fields in a table, and concerns about
"cluttering up" the table, are not really valid considerations. On the
basis of what I know so far about your database, I think I would
probably still try to keep all the Contacts information in the one
table. But, as I said, a moot point. When you say they are "referred
for different things", do you mean that the same contact can be referred
for more than one thing? And if so, can the referral source for each
"thing" be different? If so, it may be correct to have a separate table
for this referral data,... but this would be a one-to-many relationship,
and would be different from the table structure as you have described it.

 

[CS]

We would only be interested in how they _initially_ were referred,
regardless of what their first contact with the organization was (so there
would, in fact, only be one possible initial referral source), but we would
want to know what their first contact with the organization was.

I would say that about 50% of the contacts are NOT referred by someone else
(they find the organization on their own), or, they are existing contacts
from a flat-file DB that is being ported to Access, which did not collect
this information (some of which are 6 - 7 years old) -- when the DB is ready
to go, we will attempt to collect this original referral information for as
many of our existing contacts as possible, but you know how people are about
responding to a survey at times, so we may very well not get this
information. What is your opinion on that percentage of total contacts (400
of about around 800 at this point, and growing quickly) in terms of blanks
if all of this is tracked in the basic contact table?

Also, we want to gather a number of different pieces of information about
the referral, even if self-referred --
1. Type of first contact with the organization
2. Who referred them, if it is currently a contact in the DB
3. How they found us, if there was no person referring them
4. Date of first contact with organization
5. Notes about the first contact

I actually started out with all this in the tblContact, but removed it and
began working with a separate table at the suggestion of someone in this NG.
Since I am not yet truly adept at append, join, etc., I have erred
consistently on the side of more rather than less tables.

Thanks again for your help,
Carol

 

[Steve Schapel]

Carol,

Opinions may vary on this. I am not sure why someone else has advised
you the way they have. One-to-one relationships add complexity to the
data structure, so there has to be a good reason to introduce this extra
complexity. In my opinion, your database doesn't even begin to qualify.
If it was mine, all of the information you listed would absolutely,
definitely be in the tblContacts table, where you had it in the first
place. You will be able to get it to work by having it in a separate
table, but I wouldn't even think about it.

 

[CS]

Thank you for all your help, Steve -- I will go back and do it the way I
first conceived (glad to know my initial instincts weren't _all_ bad and
stick all this stuff in the tblContacts.

Thanks again for your kind attention on a Saturday --
Carol

 

[Steve Schapel]

You're wecome, Carol. Good luck with the rest of the project. By the
way, right now it's Sunday evening in New Zealand