Les Isaacs
Registered User.
- Local time
- Today, 12:11
- Joined
- May 6, 2008
- Messages
- 186
Hi All
I have a report that has the query below as its data source. The query runs fine on its own, but when I try to run the report I get:
"Multi-level GROUP BY clause is not allowed in a subquery".
How can it be that this is allowed when running the query but not when running the report?
If I delete the field 'Absences' (which is the expression given by the query's subquery) from the report, the report runs OK: but that's no good because I need 'Absences' shown on the report!!
Hope someone can help.
BTW: apologies for the naming convention.
Many thanks
Les
The query:
SELECT staffs.practice, stafpay.[month name], stafpay.paytyp, IIf([paytyp] Like "hrs/wk" & "*",[stafpay].[number],0) AS [nml_hrs/wk], IIf([paytyp]="hrs/wk",[stafpay].[number],0)*[hourly rate]/7*365/12+IIf([paytyp]="hrs/wk 52",[stafpay].[number],0)*[hourly rate]*52/12 AS [nml_hrs/wk_pay], IIf([paytyp] Like "ex hrs/wk" & "*",[stafpay].[number],0) AS [ex_hrs/wk], IIf([paytyp]="ex hrs/wk",[stafpay].[number],0)*[ex hourly rate]/7*365/12+IIf([paytyp]="ex hrs/wk 52",[stafpay].[number],0)*[ex hourly rate]*52/12 AS [ex_hrs/wk_pay], IIf([paytyp] Like "3rd hrs/wk" & "*",[stafpay].[number],0) AS [3rd_hrs/wk], IIf([paytyp]="3rd hrs/wk",[stafpay].[number],0)*[y hourly rate]/7*365/12+IIf([paytyp]="3rd hrs/wk 52",[stafpay].[number],0)*[y hourly rate]*52/12 AS [3rd_hrs/wk_pay], CDate("1 " & Mid([year],3,10)) AS Yearstart, DateAdd("yyyy",1,[Yearstart])-1 AS Yearend, (SELECT Count(*) from [SicknessAbsencesAllPracticeStaff_local] where [SicknessAbsencesAllPracticeStaff_local].[employeename]=[staffs].staff_name) AS Absences
FROM staffs INNER JOIN (months INNER JOIN stafpay ON months.[month name] = stafpay.[month name]) ON staffs.staff_name = stafpay.staff_name
WHERE (((staffs.practice)=[Forms]![frm x main]![prac name]) AND ((stafpay.[month name])=[Forms]![frm x main]![month name]) AND ((stafpay.paytyp) Like "*" & "/wk" & "*" Or (stafpay.paytyp)="basic mth pay") AND ((stafpay.prop)=1) AND ((stafpay.mthprop)=1));
I have a report that has the query below as its data source. The query runs fine on its own, but when I try to run the report I get:
"Multi-level GROUP BY clause is not allowed in a subquery".
How can it be that this is allowed when running the query but not when running the report?
If I delete the field 'Absences' (which is the expression given by the query's subquery) from the report, the report runs OK: but that's no good because I need 'Absences' shown on the report!!
Hope someone can help.
BTW: apologies for the naming convention.
Many thanks
Les
The query:
SELECT staffs.practice, stafpay.[month name], stafpay.paytyp, IIf([paytyp] Like "hrs/wk" & "*",[stafpay].[number],0) AS [nml_hrs/wk], IIf([paytyp]="hrs/wk",[stafpay].[number],0)*[hourly rate]/7*365/12+IIf([paytyp]="hrs/wk 52",[stafpay].[number],0)*[hourly rate]*52/12 AS [nml_hrs/wk_pay], IIf([paytyp] Like "ex hrs/wk" & "*",[stafpay].[number],0) AS [ex_hrs/wk], IIf([paytyp]="ex hrs/wk",[stafpay].[number],0)*[ex hourly rate]/7*365/12+IIf([paytyp]="ex hrs/wk 52",[stafpay].[number],0)*[ex hourly rate]*52/12 AS [ex_hrs/wk_pay], IIf([paytyp] Like "3rd hrs/wk" & "*",[stafpay].[number],0) AS [3rd_hrs/wk], IIf([paytyp]="3rd hrs/wk",[stafpay].[number],0)*[y hourly rate]/7*365/12+IIf([paytyp]="3rd hrs/wk 52",[stafpay].[number],0)*[y hourly rate]*52/12 AS [3rd_hrs/wk_pay], CDate("1 " & Mid([year],3,10)) AS Yearstart, DateAdd("yyyy",1,[Yearstart])-1 AS Yearend, (SELECT Count(*) from [SicknessAbsencesAllPracticeStaff_local] where [SicknessAbsencesAllPracticeStaff_local].[employeename]=[staffs].staff_name) AS Absences
FROM staffs INNER JOIN (months INNER JOIN stafpay ON months.[month name] = stafpay.[month name]) ON staffs.staff_name = stafpay.staff_name
WHERE (((staffs.practice)=[Forms]![frm x main]![prac name]) AND ((stafpay.[month name])=[Forms]![frm x main]![month name]) AND ((stafpay.paytyp) Like "*" & "/wk" & "*" Or (stafpay.paytyp)="basic mth pay") AND ((stafpay.prop)=1) AND ((stafpay.mthprop)=1));
