GROUP BY clause

[vbjohn]

I get this error message on my Report before I run it.

"Multi-level GROUP BY clause not allowed in a subquery"

Here is my query:

PARAMETERS [Beginning date] DateTime, [Ending Date] DateTime;
SELECT dbo_UPR30100.GRWGPRN, dbo_UPR30100.EMPLOYID, dbo_UPR00100.SOCSCNUM, UCase(RTrim([dbo_UPR00100].[LASTNAME])+', '+RTrim([dbo_UPR00100].[FRSTNAME])+' '+RTrim([dbo_UPR00100].[MIDLNAME])) AS Name, dbo_UPR30100.CHEKDATE, dbo_UPR00100.DEPRTMNT, ((SELECT SUM(dbo_UPR00401.HOURSWKD) FROM dbo_UPR00401 WHERE dbo_UPR00401.PAYDATE Between [Beginning date] And [Ending Date] AND dbo_UPR00401.EMPLOYID = dbo_UPR00100.EMPLOYID)/100) AS HRSWKD
FROM (dbo_UPR00100 INNER JOIN dbo_UPR00401 ON dbo_UPR00100.EMPLOYID = dbo_UPR00401.EMPLOYID) INNER JOIN dbo_UPR30100 ON dbo_UPR00100.EMPLOYID = dbo_UPR30100.EMPLOYID
WHERE (((dbo_UPR30100.CHEKDATE) Between [Beginning date] And [Ending Date]) AND ((dbo_UPR00100.DEPRTMNT)="600"));

Why wouldn't it except HRSWKD in the Group section?


Thanks...

 

[pitou]

your sql query

hello John

I read the script of your sql query which is a "bit tough" to understand.
I do not really why the error occurs, but here is perheaps one solution : to split your long query into several smaller.

for instance, transform your subquery into a simple query and save it, for example with this name : "queryB".

Re-rite your first former query with, as source in the FROM clause, queryB. (select fieldA, fieldB from queryB where etc....)
save it with an other name "queryA"

Replace the parameters with real values. Try alone "queryB" where seemingly laid the problem with the "group by". If it still does not work, simplify again your query with fewer fields, I think it is the best way to spot where it is topsy turvy...........


I hope, those small pieces of advise will help you

best regards from France

 

[vbjohn]

Thanks

Thanks pitou I got it working!!! Thanks for the advice!


John-

 

[pitou]

your query

very glad to help you !

Pitou