ChampionDuy
Registered User.
- Local time
- Today, 14:59
- Joined
- Mar 14, 2002
- Messages
- 94
I have a query Identical to this one that works fine but this one just gets the same data from a different table. Can you see whats wrong with it. I get a Message saying that it may be too complicated or complex and I may want to assign variables.
Bad one
PARAMETERS [Enter last two digits of year:] Value;
SELECT Mid$([CCN],9,4)+" " AS Region, Sum([CASE1].[Request]) AS Requests, AVG(Year) as Yr
FROM CASE1
group BY Mid$([CCN],9,4), Mid$([CCN],6,2)
HAVING (((Sum(CASE1.Request))>=1) AND ((Mid$([CCN],6,2))=[Enter last two digits of year:]) AND ((Count(CASE1.Request))>=1))
UNION select "TOTAL " AS Region, Sum([CASE1].[Request]) AS Requests, AVG(Year) as Yr from case1
HAVING (((Sum(CASE1.Request))>=1) AND ((Mid$([CCN],6,2))=[Enter last two digits of year:]) AND ((Count(CASE1.Request))>=1));
This is the one that works fine
Good one
PARAMETERS [Enter last two digits of year:] Value;
SELECT Mid$([CCN],9,4)+" " AS Region, Sum([CASEFILE].[Request]) AS Requests, AVG(Year) as Yr
FROM CASEFILE
group BY Mid$([CCN],9,4), Mid$([CCN],6,2)
HAVING (((Sum(CASEFILE.Request))>=0) AND ((Mid$([CCN],6,2))=[Enter last two digits of year:]) AND ((Count(CASEFILE.Request))>=0))
UNION select "TOTAL " AS Region, Sum([CASEFILE].[Request]) AS Requests, AVG(Year) as Yr from casefile
HAVING (((Sum(CASEFILE.Request))>=0) AND ((Mid$([CCN],6,2))=[Enter last two digits of year:]) AND ((Count(CASEFILE.Request))>=0));
Bad one
PARAMETERS [Enter last two digits of year:] Value;
SELECT Mid$([CCN],9,4)+" " AS Region, Sum([CASE1].[Request]) AS Requests, AVG(Year) as Yr
FROM CASE1
group BY Mid$([CCN],9,4), Mid$([CCN],6,2)
HAVING (((Sum(CASE1.Request))>=1) AND ((Mid$([CCN],6,2))=[Enter last two digits of year:]) AND ((Count(CASE1.Request))>=1))
UNION select "TOTAL " AS Region, Sum([CASE1].[Request]) AS Requests, AVG(Year) as Yr from case1
HAVING (((Sum(CASE1.Request))>=1) AND ((Mid$([CCN],6,2))=[Enter last two digits of year:]) AND ((Count(CASE1.Request))>=1));
This is the one that works fine
Good one
PARAMETERS [Enter last two digits of year:] Value;
SELECT Mid$([CCN],9,4)+" " AS Region, Sum([CASEFILE].[Request]) AS Requests, AVG(Year) as Yr
FROM CASEFILE
group BY Mid$([CCN],9,4), Mid$([CCN],6,2)
HAVING (((Sum(CASEFILE.Request))>=0) AND ((Mid$([CCN],6,2))=[Enter last two digits of year:]) AND ((Count(CASEFILE.Request))>=0))
UNION select "TOTAL " AS Region, Sum([CASEFILE].[Request]) AS Requests, AVG(Year) as Yr from casefile
HAVING (((Sum(CASEFILE.Request))>=0) AND ((Mid$([CCN],6,2))=[Enter last two digits of year:]) AND ((Count(CASEFILE.Request))>=0));