Tried to execute a query that does not include the specified expression 'LastName' as

[bradmay]

This is the SQL from a query for a Payroll report. It was an already existing report, so I'm trying to modify it to meet the business's needs. The problem is that there are multiple entries for WOLabor.Hours for a given day. These entries come from the invoicing program when a mechanic's hours are logged for billable time. So there may be 3 hours here and 1.5 hours there...but all for the same day. Payroll is just concerned with total hours worked...so I'm trying to combine all the hours for a given day into one variable or something and use that. I know this is long, but apparently a lot of fields are involved:

THIS GIVES ME NO ERRORS:

SELECT Person.LastName, WOLabor.MechanicNo, WOLabor.WONo, WOLabor.Hours, WOLabor.MechanicName, WOLabor.DateOfLabor, WOLabor.LaborRateType, WOLabor.SaleDept, WOLabor.SaleCode, IIf(WoLabor!laborratetype="R",IIf(wolabor!salecode  <"P",[Hours],0)) AS Regular, IIf([regular] Is Null,0,[regular]) AS Reg, IIf(WoLabor!laborratetype="P",[Hours],0) AS [Double], IIf(WoLabor!laborratetype="O",[hours],0) AS Overtime, IIf(wolabor!salecode="R",[Hours],0) AS Holiday, IIf(wolabor!salecode="V",[Hours],0) AS LT, IIf(wolabor!salecode="U",[Hours],0) AS npay, IIf(wolabor!salecode="T",[Hours],0) AS Sick, IIf(wolabor!salecode="P",[Hours],0) AS Meet, IIf(wolabor!salecode="S",[Hours],0) AS vac, IIf([Hours]<=8,[Hours],8) AS PayrollReg, IIf([Hours]>8,[Hours]-8,0) AS PayrollOT, SaleCodes.LaborDescription, WOLabor.Cost, WOLabor.Sell, Person.Branch, Branch.Name, Dept.Title
FROM (((WOLabor INNER JOIN SaleCodes ON (WOLabor.SaleBranch=SaleCodes.Branch) AND (WOLabor.SaleDept=SaleCodes.Dept) AND (WOLabor.SaleCode=SaleCodes.Code)) LEFT JOIN Person ON WOLabor.MechanicNo=Person.Number) INNER JOIN Branch ON SaleCodes.Branch=Branch.Number) INNER JOIN Dept ON (SaleCodes.Dept=Dept.Dept) AND (SaleCodes.Branch=Dept.Branch)
WHERE (((WOLabor.DateOfLabor)>=[Start Date] And (WOLabor.DateOfLabor)<DateAdd("d",1,[End Date])) AND ((Person.Branch) Like IIf([Enter the Branch Number or "All" for all:] Like "A*","*",[Enter the Branch Number or "All" for all:])) AND ((WOLabor.Transfer)=False))
ORDER BY WOLabor.SaleDept, WOLabor.SaleCode;

When I make the following changes to the SELECT section, I get this error:

You tried to execute a query that does not include the specified expression 'LastName' as part of an aggregate function.

... IIf(Sum([Hours])<=8,Sum([Hours]),8) AS PayrollReg, IIf(Sum([Hours])>8,Sum([Hours])-8,0) AS PayrollOT, ...

Any insight is greatly appreciated.

[don94403]

This is the SQL from a query for a Payroll report. It was an already existing report, so I'm trying to modify it to meet the business's needs. The problem is that there are multiple entries for WOLabor.Hours for a given day. These entries come from the invoicing program when a mechanic's hours are logged for billable time. So there may be 3 hours here and 1.5 hours there...but all for the same day. Payroll is just concerned with total hours worked...so I'm trying to combine all the hours for a given day into one variable or something and use that. I know this is long, but apparently a lot of fields are involved:

THIS GIVES ME NO ERRORS:

SELECT Person.LastName, WOLabor.MechanicNo, WOLabor.WONo, WOLabor.Hours, WOLabor.MechanicName, WOLabor.DateOfLabor, WOLabor.LaborRateType, WOLabor.SaleDept, WOLabor.SaleCode, IIf(WoLabor!laborratetype="R",IIf(wolabor!salecode  <"P",[Hours],0)) AS Regular, IIf([regular] Is Null,0,[regular]) AS Reg, IIf(WoLabor!laborratetype="P",[Hours],0) AS [Double], IIf(WoLabor!laborratetype="O",[hours],0) AS Overtime, IIf(wolabor!salecode="R",[Hours],0) AS Holiday, IIf(wolabor!salecode="V",[Hours],0) AS LT, IIf(wolabor!salecode="U",[Hours],0) AS npay, IIf(wolabor!salecode="T",[Hours],0) AS Sick, IIf(wolabor!salecode="P",[Hours],0) AS Meet, IIf(wolabor!salecode="S",[Hours],0) AS vac, IIf([Hours]<=8,[Hours],8) AS PayrollReg, IIf([Hours]>8,[Hours]-8,0) AS PayrollOT, SaleCodes.LaborDescription, WOLabor.Cost, WOLabor.Sell, Person.Branch, Branch.Name, Dept.Title
FROM (((WOLabor INNER JOIN SaleCodes ON (WOLabor.SaleBranch=SaleCodes.Branch) AND (WOLabor.SaleDept=SaleCodes.Dept) AND (WOLabor.SaleCode=SaleCodes.Code)) LEFT JOIN Person ON WOLabor.MechanicNo=Person.Number) INNER JOIN Branch ON SaleCodes.Branch=Branch.Number) INNER JOIN Dept ON (SaleCodes.Dept=Dept.Dept) AND (SaleCodes.Branch=Dept.Branch)
WHERE (((WOLabor.DateOfLabor)>=[Start Date] And (WOLabor.DateOfLabor)<DateAdd("d",1,[End Date])) AND ((Person.Branch) Like IIf([Enter the Branch Number or "All" for all:] Like "A*","*",[Enter the Branch Number or "All" for all:])) AND ((WOLabor.Transfer)=False))
ORDER BY WOLabor.SaleDept, WOLabor.SaleCode;

When I make the following changes to the SELECT section, I get this error:

You tried to execute a query that does not include the specified expression 'LastName' as part of an aggregate function.

... IIf(Sum([Hours])<=8,Sum([Hours]),8) AS PayrollReg, IIf(Sum([Hours])>8,Sum([Hours])-8,0) AS PayrollOT, ...

Any insight is greatly appreciated.

I'm afraid I don't have much time right now, but let me give you at least a start. You're talking about a Totals (or Aggregate) Query, which means that you can use the SUM() function, but you have to tell it what field to GROUP BY, in this case probably your LastName field. Your query is rather complicated, so I won't try to give you the full syntax. Perhaps you can figure it out with the above explanation, or maybe someone else will post the syntax for you.

[Wolffy]

Don's got it, but I'll just expand. In short, you need to include EVERY column that does NOT appear in an Aggregate function (like SUM()) in your GROUP BY column.

[EDIT]
I just looked back at your query and the IIF functions would make the GROUP BY rather horrific. In you case, I would consider makeing a stored query (named query) without trying to do the IIF stuff and then use a second query on the first to do the IIF logic. To me, this makes it easier to write and understand. It seem too many shy away from named queires and view when they really make you life easier.
[/EDIT]

[bradmay]

Don's got it, but I'll just expand. In short, you need to include EVERY column that does NOT appear in an Aggregate function (like SUM()) in your GROUP BY column.

[EDIT]
I just looked back at your query and the IIF functions would make the GROUP BY rather horrific. In you case, I would consider makeing a stored query (named query) without trying to do the IIF stuff and then use a second query on the first to do the IIF logic. To me, this makes it easier to write and understand. It seem too many shy away from named queires and view when they really make you life easier.
[/EDIT]

Thanks guys for all the help. I will try to do that and I'll post back if I get stuck. I'm new to this job and this is the first time I'm having to use Access and DB stuff in a job. It's fun though.

[bradmay]

Ok....I thought I knew what to do...but I'm having trouble getting started. Can someone explain this named query thing some more? Do I delete some parts of the first query and put it into a separate query and then get values from new query into the old one? How do I do this?

[Wolffy]

You have the right idea. Create a query that has the SUM() and GROUP BY stuff without the IIF functions and save it with a name -- like Fred, only more specific to what you are doing. You can then use that query in another query, such as:

SELECT IIF(PayRollReg<=8, PayRollReg, 8) as PayRollReg ....
  FROM FRED

Hopefully this will point you in the right direction -- if you get stuck, post what you have so far are we'll go from there!=.

[bradmay]

You have the right idea. Create a query that has the SUM() and GROUP BY stuff without the IIF functions and save it with a name -- like Fred, only more specific to what you are doing. You can then use that query in another query, such as:

SELECT IIF(PayRollReg<=8, PayRollReg, 8) as PayRollReg ....
  FROM FRED

Hopefully this will point you in the right direction -- if you get stuck, post what you have so far are we'll go from there!=.

I've created a separate query to get a sum for hours worked each day...which is what I need to use. But I get an outer join error. Here's the error:

'The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the the joins to e performed first, create a separate query that performs the first join and then include that query in your SQL statement.'

My new query (Labor Hours Query):

SELECT DISTINCTROW WOLabor.DateOfLabor, WOLabor.MechanicNo, Sum(WOLabor.Hours) AS DailyHours, Person.LastName
FROM WOLabor INNER JOIN Person ON WOLabor.MechanicNo = Person.Number
GROUP BY WOLabor.DateOfLabor, WOLabor.MechanicNo, Person.LastName;

My main query with the new query added:

SELECT Person.LastName, …  WOLabor.SaleCode, 
IIf(…all the same IIf statements…) AS PayrollOT,
 SaleCodes.LaborDescription, … Dept.Title
FROM [Labor Hours Query]
 INNER JOIN ((((WOLabor INNER JOIN SaleCodes ON (WOLabor.SaleBranch = SaleCodes.Branch) AND (WOLabor.SaleDept = SaleCodes.Dept) AND (WOLabor.SaleCode = SaleCodes.Code)) LEFT JOIN Person ON WOLabor.MechanicNo = Person.Number) INNER JOIN Branch ON SaleCodes.Branch = Branch.Number) INNER JOIN Dept ON (SaleCodes.Dept = Dept.Dept) AND (SaleCodes.Branch = Dept.Branch)) ON [Labor Hours Query].MechanicNo = Person.Number
WHERE (((WOLabor.DateOfLabor)>=[Start Date] And (WOLabor.DateOfLabor)<DateAdd("d",1,[End Date])) AND ((Person.Branch) Like IIf([Enter the Branch Number or "All" for all:] Like "A*","*",[Enter the Branch Number or "All" for all:])) AND ((WOLabor.Transfer)=False))
ORDER BY WOLabor.SaleDept, WOLabor.SaleCode;

I really am grateful for all your help on this. I know it's a lot of code and a lot to ask.