Re: Query Me This -- two references to another tables field

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?

--
Steve Schapel, Microsoft Access MVP


CS wrote: 
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: 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)
  • Query Me This -- two references to another tables field
    ... the ContactID from tblContacts is referenced twice -- once ... as the Primary Key RContactID which says who is being referred, ... and the person referring them. ... tbleContacts.ContactIDtblReferrals.RContactID, I can concatenate the ...
    (microsoft.public.access.gettingstarted)
  • Re: Insert into script
    ... I am referring to the columns within the primary key. ... it fails because you are trying to insert duplicate data*/ ... > to the fields that I am inserting, i.e. entitiycategorydatabaseid? ...
    (microsoft.public.sqlserver.programming)
  • Sort based on the name selected under the combo box
    ... I have one form with a drop-down combo box in which I ... And this form is referring to the query and I selected the ... sort: ... My table has primary key and company ...
    (microsoft.public.access.forms)