Self join?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi. I'm trying to create a query that returns the Contact field twice, if
applicatble- once as the original contact and once as the "ITA" contact. So
a person can be both the Contact and the ITA Contact (although, hopefully not
on the same record).

For example Susie Qualye is a Contact and her ITA contact is Jeff Parker
(who is also a Contact in his own right). Sort of a Big Brother concept,
where Susie in turn could be a Big Sister to someone...

I can get the ITAContactID to populate (it's really the same as the
ContactID- I just used a combo box to select ContactID as ITAContactID), but
I can't get the [SONm] field to populate based on the ITAContactID.


SELECT DISTINCT c1.ContactID, (SELECT c2.[FirstName] & " " & c2.[LastName]
AS [SONm] FROM Contacts c2 WHERE c2.ContactID = ITAContactID) AS [ITA Name],
nz([Donor Name]) & [Contact Name] AS Name1, nz(c1.[FirstName]) & " " &
c1.[LastName] AS [Donor Name], nz(c1.[ContactFirstName]) & " " &
c1.[ContactLastName] AS [Contact Name], nz(c1.[CompanyName]," ") AS [Company
Name], c1.[MailingAddress] & ", "+c1.[OptAddress] AS Address, c1.City,
c1.StateOrProvince, c1.PostalCode, c1.AskAgain, c1.DateSent, c1.ITAContactID
FROM Contacts AS c1, Contacts AS c2
WHERE (((c1.AskAgain)=Yes) AND ((c1.DateSent) Is Null) AND
((c1.ITAContactID)>[c2].[ContactID] Or (c1.ITAContactID) Is Null)) OR
(((c1.AskAgain)=Yes) AND ((c1.DateSent)<DateSerial(Year(Date()),10,1)) AND
((c1.ITAContactID)<[c2].[ContactID] Or (c1.ITAContactID) Is Null));

Any suggestions? I have a hard enough time with sql when I'm not trying to
do anything fancy! Thanks for your time!
 
Back
Top