Error - CANNOT HAVE AGGREGATE FUNCTION IN EXPRESSION

  • Thread starter Thread starter Ann Scharpf
  • Start date Start date
A

Ann Scharpf

I have a query where I am calculating labor costs. It was working until I
tried to add a calculation to add the cost of living allowance (COLA) if the
pay period ending date is greater than the new year.

Here is the SQL for the whole query:

______
SELECT DISTINCT eBizHoursRecorded.Employee, eBizHoursRecorded.Project,
eBizHoursRecorded.Vendor, eBizHoursRecorded.PPEdate,
eBizHoursRecorded.HourType, Sum(eBizHoursRecorded.TotalHours) AS
SumOfTotalHours, Sum(eBizHoursRecorded.NightHours) AS SumOfNightHours,
GovtEmployeeGradeStep.Grade, GovtEmployeeGradeStep.Step,
HourTypeCodes.Category,
Sum(IIf([eBizHoursRecorded.HourType]="HG",[Base]*[TotalHours]*2,IIf([HourTypeCodes.OTPayRate]=True,[Overtime]*[TotalHours],IIf([eBizHoursRecorded.HourType]="SG",[Base]*[TotalHours]*1.25,IIf([Unpaid]=True,0,[Base]*[TotalHours])))))+Sum([NightHours]*0.1)+Sum([TotalHours]*[BenefitsRate]*[Base])
AS Cost,
Sum(IIf([PPEdate]<[NewYear],(IIf([eBizHoursRecorded.HourType]="HG",[Base]*[TotalHours]*2,IIf([HourTypeCodes.OTPayRate]=True,[Overtime]*[TotalHours],IIf([eBizHoursRecorded.HourType]="SG",[Base]*[TotalHours]*1.25,IIf([Unpaid]=True,0,[Base]*[TotalHours])))))+Sum([NightHours]*0.1)+Sum([TotalHours]*[BenefitsRate]*[Base]),((IIf([eBizHoursRecorded.HourType]="HG",[Base]*[TotalHours]*2,IIf([HourTypeCodes.OTPayRate]=True,[Overtime]*[TotalHours],IIf([eBizHoursRecorded.HourType]="SG",[Base]*[TotalHours]*1.25,IIf([Unpaid]=True,0,[Base]*[TotalHours])))))+Sum([NightHours]*0.1)+Sum([TotalHours]*[BenefitsRate]*[Base])*(1+[COLA]))))
AS Cost2, GovtWageBaseOvertime.Base, GovtWageBaseOvertime.Overtime
FROM GovtRatePercentages, ((eBizHoursRecorded INNER JOIN
GovtEmployeeGradeStep ON eBizHoursRecorded.Employee =
GovtEmployeeGradeStep.Employee) INNER JOIN HourTypeCodes ON
eBizHoursRecorded.HourType = HourTypeCodes.HourType) INNER JOIN
GovtWageBaseOvertime ON (GovtEmployeeGradeStep.Step =
GovtWageBaseOvertime.Step) AND (GovtEmployeeGradeStep.Grade =
GovtWageBaseOvertime.Grade)
WHERE (((eBizHoursRecorded.Employee) Like "Both*") AND
((GovtEmployeeGradeStep.EffectiveDate)=(select top 1 effectivedate from
GovtEmployeeGradeStep where effectivedate <= eBizHoursRecorded.PPEdate and
eBizHoursRecorded.Employee = GovtEmployeeGradeStep.Employee order by
effectivedate desc)) AND ((eBizHoursRecorded.HourType) Not Like 'L*'))
GROUP BY eBizHoursRecorded.Employee, eBizHoursRecorded.Project,
eBizHoursRecorded.Vendor, eBizHoursRecorded.PPEdate,
eBizHoursRecorded.HourType, GovtEmployeeGradeStep.Grade,
GovtEmployeeGradeStep.Step, HourTypeCodes.Category,
GovtWageBaseOvertime.Base, GovtWageBaseOvertime.Overtime
HAVING (((HourTypeCodes.Category)<>'Leave'));

_____

It was working fine until I added this field:

Cost2:
Sum(IIf([PPEdate]<[NewYear],(IIf([eBizHoursRecorded.HourType]="HG",[Base]*[TotalHours]*2,IIf([HourTypeCodes.OTPayRate]=True,[Overtime]*[TotalHours],IIf([eBizHoursRecorded.HourType]="SG",[Base]*[TotalHours]*1.25,IIf([Unpaid]=True,0,[Base]*[TotalHours])))))+Sum([NightHours]*0.1)+Sum([TotalHours]*[BenefitsRate]*[Base]),((IIf([eBizHoursRecorded.HourType]="HG",[Base]*[TotalHours]*2,IIf([HourTypeCodes.OTPayRate]=True,[Overtime]*[TotalHours],IIf([eBizHoursRecorded.HourType]="SG",[Base]*[TotalHours]*1.25,IIf([Unpaid]=True,0,[Base]*[TotalHours])))))+Sum([NightHours]*0.1)+Sum([TotalHours]*[BenefitsRate]*[Base])*(1+[COLA]))))

_____

I always get confused with these aggregate function problems. Can anyone
direct me about what I did wrong in that formula? Basically I tried to do
the following

If PPEDate<New Year, do all the calculations for each hour type, do all the
calculations for hour type and multiply by (1+COLA)

_____

Thanks so much for any help you can give me.
 
Try add the COLA field to the GROUP BY clause.
--
Duane Hookom
Microsoft Access MVP


Ann Scharpf said:
I have a query where I am calculating labor costs. It was working until I
tried to add a calculation to add the cost of living allowance (COLA) if the
pay period ending date is greater than the new year.

Here is the SQL for the whole query:

______
SELECT DISTINCT eBizHoursRecorded.Employee, eBizHoursRecorded.Project,
eBizHoursRecorded.Vendor, eBizHoursRecorded.PPEdate,
eBizHoursRecorded.HourType, Sum(eBizHoursRecorded.TotalHours) AS
SumOfTotalHours, Sum(eBizHoursRecorded.NightHours) AS SumOfNightHours,
GovtEmployeeGradeStep.Grade, GovtEmployeeGradeStep.Step,
HourTypeCodes.Category,
Sum(IIf([eBizHoursRecorded.HourType]="HG",[Base]*[TotalHours]*2,IIf([HourTypeCodes.OTPayRate]=True,[Overtime]*[TotalHours],IIf([eBizHoursRecorded.HourType]="SG",[Base]*[TotalHours]*1.25,IIf([Unpaid]=True,0,[Base]*[TotalHours])))))+Sum([NightHours]*0.1)+Sum([TotalHours]*[BenefitsRate]*[Base])
AS Cost,
Sum(IIf([PPEdate]<[NewYear],(IIf([eBizHoursRecorded.HourType]="HG",[Base]*[TotalHours]*2,IIf([HourTypeCodes.OTPayRate]=True,[Overtime]*[TotalHours],IIf([eBizHoursRecorded.HourType]="SG",[Base]*[TotalHours]*1.25,IIf([Unpaid]=True,0,[Base]*[TotalHours])))))+Sum([NightHours]*0.1)+Sum([TotalHours]*[BenefitsRate]*[Base]),((IIf([eBizHoursRecorded.HourType]="HG",[Base]*[TotalHours]*2,IIf([HourTypeCodes.OTPayRate]=True,[Overtime]*[TotalHours],IIf([eBizHoursRecorded.HourType]="SG",[Base]*[TotalHours]*1.25,IIf([Unpaid]=True,0,[Base]*[TotalHours])))))+Sum([NightHours]*0.1)+Sum([TotalHours]*[BenefitsRate]*[Base])*(1+[COLA]))))
AS Cost2, GovtWageBaseOvertime.Base, GovtWageBaseOvertime.Overtime
FROM GovtRatePercentages, ((eBizHoursRecorded INNER JOIN
GovtEmployeeGradeStep ON eBizHoursRecorded.Employee =
GovtEmployeeGradeStep.Employee) INNER JOIN HourTypeCodes ON
eBizHoursRecorded.HourType = HourTypeCodes.HourType) INNER JOIN
GovtWageBaseOvertime ON (GovtEmployeeGradeStep.Step =
GovtWageBaseOvertime.Step) AND (GovtEmployeeGradeStep.Grade =
GovtWageBaseOvertime.Grade)
WHERE (((eBizHoursRecorded.Employee) Like "Both*") AND
((GovtEmployeeGradeStep.EffectiveDate)=(select top 1 effectivedate from
GovtEmployeeGradeStep where effectivedate <= eBizHoursRecorded.PPEdate and
eBizHoursRecorded.Employee = GovtEmployeeGradeStep.Employee order by
effectivedate desc)) AND ((eBizHoursRecorded.HourType) Not Like 'L*'))
GROUP BY eBizHoursRecorded.Employee, eBizHoursRecorded.Project,
eBizHoursRecorded.Vendor, eBizHoursRecorded.PPEdate,
eBizHoursRecorded.HourType, GovtEmployeeGradeStep.Grade,
GovtEmployeeGradeStep.Step, HourTypeCodes.Category,
GovtWageBaseOvertime.Base, GovtWageBaseOvertime.Overtime
HAVING (((HourTypeCodes.Category)<>'Leave'));

_____

It was working fine until I added this field:

Cost2:
Sum(IIf([PPEdate]<[NewYear],(IIf([eBizHoursRecorded.HourType]="HG",[Base]*[TotalHours]*2,IIf([HourTypeCodes.OTPayRate]=True,[Overtime]*[TotalHours],IIf([eBizHoursRecorded.HourType]="SG",[Base]*[TotalHours]*1.25,IIf([Unpaid]=True,0,[Base]*[TotalHours])))))+Sum([NightHours]*0.1)+Sum([TotalHours]*[BenefitsRate]*[Base]),((IIf([eBizHoursRecorded.HourType]="HG",[Base]*[TotalHours]*2,IIf([HourTypeCodes.OTPayRate]=True,[Overtime]*[TotalHours],IIf([eBizHoursRecorded.HourType]="SG",[Base]*[TotalHours]*1.25,IIf([Unpaid]=True,0,[Base]*[TotalHours])))))+Sum([NightHours]*0.1)+Sum([TotalHours]*[BenefitsRate]*[Base])*(1+[COLA]))))

_____

I always get confused with these aggregate function problems. Can anyone
direct me about what I did wrong in that formula? Basically I tried to do
the following

If PPEDate<New Year, do all the calculations for each hour type, do all the
calculations for hour type and multiply by (1+COLA)

_____

Thanks so much for any help you can give me.
Click to expand...
 
Hi, Dwight:

Thanks for the response. I tried adding the COLA to the group by clause and
I still got the same error.

I then tried entirely REMOVING the COLA field (and the table where the COLA
is stored) from the query. I hard coded a COLA amount into the formula and
still got the same error. I think the problem is in the way I wrote this
particular formula. The bottom of this post shows the formula is with a
number in place of the field (it's the 1.032).

If I have to, I can always feed the results of this query into another query
and do the COLA calculation there. I was just trying to keep things clean
with fewer tables. If you can think of some way to do this, I'd really
appreciate finding out. Thanks again.

Cost2:
Sum(IIf([PPEdate]<[NewYear],(IIf([eBizHoursRecorded.HourType]="HG",[Base]*[TotalHours]*2,IIf([HourTypeCodes.OTPayRate]=True,[Overtime]*[TotalHours],IIf([eBizHoursRecorded.HourType]="SG",[Base]*[TotalHours]*1.25,IIf([Unpaid]=True,0,[Base]*[TotalHours])))))+Sum([NightHours]*0.1)+Sum([TotalHours]*[BenefitsRate]*[Base]),((IIf([eBizHoursRecorded.HourType]="HG",[Base]*[TotalHours]*2,IIf([HourTypeCodes.OTPayRate]=True,[Overtime]*[TotalHours],IIf([eBizHoursRecorded.HourType]="SG",[Base]*[TotalHours]*1.25,IIf([Unpaid]=True,0,[Base]*[TotalHours])))))+Sum([NightHours]*0.1)+Sum([TotalHours]*[BenefitsRate]*[Base])*1.032)))
 
I don't care for all the nested IIf()s and hard-coded values in your
expressions. Any value in quotes or numbers are what I consider hard-coded
and might cause issues in the future.

I would probably create a small user-defined function in a module of
"modBusinessCalcs" that would accept the values and return the appropriate
number. Your function can be called from anywhere in your application and can
include comments, constants, assumptions, etc.

--
Duane Hookom
Microsoft Access MVP


Ann Scharpf said:
Hi, Dwight:

Thanks for the response. I tried adding the COLA to the group by clause and
I still got the same error.

I then tried entirely REMOVING the COLA field (and the table where the COLA
is stored) from the query. I hard coded a COLA amount into the formula and
still got the same error. I think the problem is in the way I wrote this
particular formula. The bottom of this post shows the formula is with a
number in place of the field (it's the 1.032).

If I have to, I can always feed the results of this query into another query
and do the COLA calculation there. I was just trying to keep things clean
with fewer tables. If you can think of some way to do this, I'd really
appreciate finding out. Thanks again.

Cost2:
Sum(IIf([PPEdate]<[NewYear],(IIf([eBizHoursRecorded.HourType]="HG",[Base]*[TotalHours]*2,IIf([HourTypeCodes.OTPayRate]=True,[Overtime]*[TotalHours],IIf([eBizHoursRecorded.HourType]="SG",[Base]*[TotalHours]*1.25,IIf([Unpaid]=True,0,[Base]*[TotalHours])))))+Sum([NightHours]*0.1)+Sum([TotalHours]*[BenefitsRate]*[Base]),((IIf([eBizHoursRecorded.HourType]="HG",[Base]*[TotalHours]*2,IIf([HourTypeCodes.OTPayRate]=True,[Overtime]*[TotalHours],IIf([eBizHoursRecorded.HourType]="SG",[Base]*[TotalHours]*1.25,IIf([Unpaid]=True,0,[Base]*[TotalHours])))))+Sum([NightHours]*0.1)+Sum([TotalHours]*[BenefitsRate]*[Base])*1.032)))
Click to expand...
 
The hard coded values are hour types that are used on the government
timekeeping system. They've been used for years and will not be subject to
change. The values are stored in a table that's extracted from the actual
timekeeping system.

I'm not an advanced enough Access developer to create a function in a
module. To tell you the truth, I'm not even sure what you're talking about.

I'm leaving this job in two days and am trying to get the database
redesigned to accommodate the new billing formulas that our site is changing
to as of the first of the new fiscal year (Oct. 1). We use to charge a flat
rate for every employee, regardless of what they earned. Now we are going to
charge the employee's actual rate multiplied by the hours they worked.
Government personnel are paid all kinds of differentials based on the hour
type: Sunday, night, overtime, holiday etc.

My plate is very full trying to finish writing desk procedures, finish
regular fiscal year end tasks, and train my replacements in my
responsibilities. So I'll just feed the results of this query into a second
query that will check the PPEDate to see if it is after the first of the new
year.

Thanks again for trying to help me.
--
Ann Scharpf


Duane Hookom said:
I don't care for all the nested IIf()s and hard-coded values in your
expressions. Any value in quotes or numbers are what I consider hard-coded
and might cause issues in the future.

I would probably create a small user-defined function in a module of
"modBusinessCalcs" that would accept the values and return the appropriate
number. Your function can be called from anywhere in your application and can
include comments, constants, assumptions, etc.

--
Duane Hookom
Microsoft Access MVP


Ann Scharpf said:
Hi, Dwight:

Thanks for the response. I tried adding the COLA to the group by clause and
I still got the same error.

I then tried entirely REMOVING the COLA field (and the table where the COLA
is stored) from the query. I hard coded a COLA amount into the formula and
still got the same error. I think the problem is in the way I wrote this
particular formula. The bottom of this post shows the formula is with a
number in place of the field (it's the 1.032).

If I have to, I can always feed the results of this query into another query
and do the COLA calculation there. I was just trying to keep things clean
with fewer tables. If you can think of some way to do this, I'd really
appreciate finding out. Thanks again.

Cost2:
Sum(IIf([PPEdate]<[NewYear],(IIf([eBizHoursRecorded.HourType]="HG",[Base]*[TotalHours]*2,IIf([HourTypeCodes.OTPayRate]=True,[Overtime]*[TotalHours],IIf([eBizHoursRecorded.HourType]="SG",[Base]*[TotalHours]*1.25,IIf([Unpaid]=True,0,[Base]*[TotalHours])))))+Sum([NightHours]*0.1)+Sum([TotalHours]*[BenefitsRate]*[Base]),((IIf([eBizHoursRecorded.HourType]="HG",[Base]*[TotalHours]*2,IIf([HourTypeCodes.OTPayRate]=True,[Overtime]*[TotalHours],IIf([eBizHoursRecorded.HourType]="SG",[Base]*[TotalHours]*1.25,IIf([Unpaid]=True,0,[Base]*[TotalHours])))))+Sum([NightHours]*0.1)+Sum([TotalHours]*[BenefitsRate]*[Base])*1.032)))
--
Ann Scharpf


Duane Hookom said:
Try add the COLA field to the GROUP BY clause.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top