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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER