#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Apr 2008
    Posts
    4
    Rep Power
    0

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


    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:

    Code:
    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.

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

    Any insight is greatly appreciated.
  2. #2
  3. Contributing User
    ASP High Scholar (3500 - 3999 posts)

    Join Date
    Jan 2007
    Location
    Northern California
    Posts
    3,536
    Rep Power
    763
    Originally Posted by 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:

    Code:
    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.

    Code:
    ... 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.
    Experience is the thing you have left when everything else is gone.
  4. #3
  5. Slaprentice of Wolves
    ASP Good Citizen (1000 - 1499 posts)

    Join Date
    Aug 2007
    Location
    Mossville, IL
    Posts
    1,469
    Rep Power
    539
    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]

    Comments on this post

    • don94403 agrees
    Last edited by Wolffy; April 19th, 2008 at 01:02 PM.
    Wolffy
    ------------------------
    Opinions expressed are my own and do not necessity reflect those of any sane person. Any code provided is intended to be an example and is provided AS IS. Rework for your specific environment may be required. Void where prohibited by law. Not valid in California. Your mileage may vary. Not FDIC insured
  6. #4
  7. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Apr 2008
    Posts
    4
    Rep Power
    0

    Named query


    Originally Posted by 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]
    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.
  8. #5
  9. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Apr 2008
    Posts
    4
    Rep Power
    0

    Help on the Named Query


    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?
  10. #6
  11. Slaprentice of Wolves
    ASP Good Citizen (1000 - 1499 posts)

    Join Date
    Aug 2007
    Location
    Mossville, IL
    Posts
    1,469
    Rep Power
    539
    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:
    Code:
    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!=.
  12. #7
  13. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Apr 2008
    Posts
    4
    Rep Power
    0

    Ok I'm stuck...


    Originally Posted by 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:
    Code:
    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):

    Code:
    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:

    Code:
    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.

Similar Threads

  1. missing operator in query expression...
    By Pluto in forum ASP Development
    Replies: 2
    Last Post: September 28th, 2005, 04:23 PM
  2. How to write conditional expression in Query?
    By dawnong in forum Microsoft Access Help
    Replies: 4
    Last Post: March 16th, 2005, 11:02 AM
  3. Execute an Access Query
    By mathbenchmark in forum ASP Development
    Replies: 2
    Last Post: December 12th, 2004, 04:28 PM
  4. syntax error(missing operator)in query expression
    By wingshya in forum .NET Development
    Replies: 5
    Last Post: September 12th, 2003, 12:20 PM
  5. Error in Query Expression
    By Steve Schofield in forum ASP Development
    Replies: 2
    Last Post: March 2nd, 2002, 12:21 AM

IMN logo majestic logo threadwatch logo seochat tools logo