troubleshooting Question
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Learn from the best
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.