Link to home
Get AccessLog in
Avatar of stankstank
stankstank

asked on

Subquery doesn't work right - does weird things! Help!

Dear Experts,

I am trying to make a query which returns all contacts who have referred at least 3 contacts within the last XXX days.  In this example, I have entered the value of 360 (1 year).  I have not even tried to figure out how to calculate the total number of referrals given becuase it gets really weird then.

It does not pull the values correctly...The query I am posting LOOKS like it works...but it doesn't.  It's just not accurate and I don't know what I have done wrong.  Please view line 17 where the code is 'WHERE Referred_By = Contact_Auto_ID)' - notice that the ')' character closes the select statement.  If I put this anywhere else, I get a really jacked up set of data...  Just an example of one problem I am having.

Here is how my table looks (I have taken out some of the columns which don't matter like names & phone number columns... you can see those in the select statement on the link)

[Contacts].Contacts
--------------------
Contact_Auto_ID (pk, int)
Date_Added (smalldatetime)
Referred_By (int - fk to Contact_Auto_ID in this table)

The column 'Referred_By' must be related to Contact_Auto_ID so that's why it's a FK to a column in the same table...  There is a default value in Contact_Auto_ID of 0.  That's how we have Referred_By set to 0 in the columns which weren't referred by another contact.

Another weird thing..in the current query, the column 'PhoneHome' has a value of null and it shows the record.  If I enter a phone number, that record goes away.  I have NO IDEA WHY!!!

I have been working with this for several hours now and still no luck...  Please help!

Here is the link to my query:
http://75.33.104.230/code/query.aspx
SOLUTION
Avatar of appari
appari
Flag of India image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
SOLUTION
Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Avatar of stankstank
stankstank

ASKER

Hey guys, thanks for the prompt response... I took a combination of these suggestions and came up with a working solution.  Thank you all so much!