Query Me This -- two references to another tables field

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



.

Relevant Pages

  • Re: Evaluate "nested-merge" function?
    ... I think the function you are referring to is CONCATENATE. ... To evaluate the composite string you have built up as if it were a ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Query Me This -- two references to another tables field
    ... stick all this stuff in the tblContacts. ... Carol ... In my opinion, your database doesn't even begin to qualify. ... if there was no person referring them ...
    (microsoft.public.access.gettingstarted)
  • Re: Query Me This -- two references to another tables field
    ... One copy will be joined to the RContactID and the other to the ReferredByContactID. ... So why not just put a ReferredBy field in the tblContacts table, and remove the tbleReferrals table altogether? ... tblContacts -- primary key is ContactID, autonumber -- general contact info tblReferrals -- primary key is RContactID, number, no duplicates -- table contains info about who referred who. ... tbleContacts.ContactIDtblReferrals.RContactID, I can concatenate the name of the person being referred, but not the name of the person referring them. ...
    (microsoft.public.access.gettingstarted)