Need Help With Query

[peryan77]

I would like to return those records where Action = "Change Plan" for 3 months in a row (past three months) for all the users.

These are the users where we have to change their plan because it has been recommended to do so three times in a row.

One record would look like this:
Recommended_Plan Action USERID Date Current Plan
---------------- ------ ------ ------ ------------
RAS Change AUT1CXG 04/01/2003 VPN

Here is how I did it, it works, but it is definetely not the way to do it because what if i wanted to return records where the value occurs 8 times in a row? Do I add 8 of the same tables?

sQ_Plan_Change is One Query. I just added this query to a subquery 3 times.

SELECT T_Users.USERID, T_Users.LastName, T_Users.FirstName
FROM ((sQ_Plan_Change RIGHT JOIN T_Users ON sQ_Plan_Change.USERID = T_Users.USERID) LEFT JOIN sQ_Plan_Change AS sQ_Plan_Change_1 ON T_Users.USERID = sQ_Plan_Change_1.USERID) LEFT JOIN sQ_Plan_Change AS sQ_Plan_Change_2 ON T_Users.USERID = sQ_Plan_Change_2.USERID
WHERE (((sQ_Plan_Change.Date)=#2/1/2004#) AND ((sQ_Plan_Change_1.Date)=#3/1/2004#) AND ((sQ_Plan_Change_2.Date)=#4/1/2004#) AND ((sQ_Plan_Change.Action)="Change Plan") AND ((sQ_Plan_Change_1.Action)="Change Plan") AND ((sQ_Plan_Change_2.Action)="Change Plan"));

What is the most effective way to do this?

 

[willir]

peryan77

I reviewed your query, and I think some essential information may be missing....

Your end results - out is...
Recommended_Plan Action USERID Date Current Plan

But your query returns...
SELECT T_Users.USERID, T_Users.LastName, T_Users.FirstName

Based on your query, your tables, so far are...
T_Users
USERID - pk
LastName
FirstName

sQ_Plan_Change
UserID
Action
Date

What is puzzling is...
- Where does Current_Plan and Recommended_Plan come from, and are these the same fields?
- You seem to be selecting from the same table, sQ_Plan_Change, three times with hardcoded dates - do you have use such a particular approach everytime you run this query? (I think this is primary issue with your post)

Also - NOTE: DATE is a reserved word, and you seem to be using it as a field name - dangerous.

Assuming Recommended_Plan and the Current_Plan are part of the table sQ_Plan_Change, then the following may work for you...

SELECT T.IserID, Format(CDate, "mmm") as Month, Count(Action) as Num_Recommendations, Recommended_Plan
FROM T_User as T, SQ_Plan_Change as P
WHERE P.Action="Change" and T.Userid = P.Userid
GROUP BY T.UserID, Format(CDate, "mmm"), Recommended_Plan;

(I used CDate instead of Date for the name of your variable)

You can tweak this code...
- For quarters, Format(CDate, "q")
- Expand upon the WHERE clause to select before or afteyr a specific date.

I am not sure I answered your question, but hopefully, I gave you some ideas.

Richard