Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2006
    Posts
    18

    Unanswered: Group Query - how to sum all fields across

    Maybe the week has just been too long.

    I have a table

    empl date reghrs OT holiday holwrkd sick callout

    I am bringing a month's worth of records into a query, grouping on empl where date is between yada, yada and summing the hours in each field.

    I want to get a total hours ACROSS for this....total of reghrs,OT,Hol for each empl..

    I've tried Field1+Field2+Field3 in a new "totals" field...tried updating the table, tried "setvalue" (or Me.totals= Me.Reghrs+Me.OT+Me.Holiday, etc) and I'm missing something here.


    thank you for your help.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Fancy posting the whole query you have so far? You're not that far off the mark, other than I don't see a Sum() function anywhere there.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Oct 2006
    Posts
    18

    thank you for looking

    All the little letters just stand for categories of exception hours. Believe me, it wasn't my idea.


    SELECT [Lname] & ", " & [Fname] AS Name, Employees.[Empl#], Sum(Hours.RegHrs) AS SumOfRegHrs, Sum(Hours.OT) AS SumOfOT, Sum(Hours.C) AS SumOfC, Sum(Hours.OC) AS SumOfOC, Sum(Hours.H) AS SumOfH, Sum(Hours.V) AS SumOfV, Sum(Hours.S) AS SumOfS, Sum(Hours.HW) AS SumOfHW, Sum(Hours.LWOP) AS SumOfLWOP, Sum([RegHrs]+[OT]+[C]+[OC]+[V}+[S]+[H]+[HW]+[LWOP]+[F]+[N]+[J]) AS Total
    FROM Employees LEFT JOIN Hours ON Employees.[Empl#] = Hours.[Empl#]
    WHERE (((Hours.Date) Between [Forms]![Frm-RptSelect]![BeginDate] And [Forms]![Frm-RptSelect]![EndDate]) AND ((Employees.Hourly)=-1))
    GROUP BY [Lname] & ", " & [Fname], Employees.[Empl#]
    ORDER BY [Lname] & ", " & [Fname];

  4. #4
    Join Date
    Oct 2006
    Posts
    18
    Ooops...that's really a ] after the V... I corrected it and it still doesn't work

  5. #5
    Join Date
    Oct 2006
    Posts
    18
    I did not let my values in the field default to "0", so the only values are what have been entered. Is having most of the fields empty my problem?

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    If the fields are Null and not 0 then you will have problems (if I understand your question). You can use the Nz function to change Nulls to zeroes.

    Ex.

    Sum(Nz(Hours.RegHrs,0))

  7. #7
    Join Date
    Oct 2006
    Posts
    18

    Group Sum Query....answer

    Yes, thank you...the blank fields were the problem. The Nz() function helps a lot. I had taken the zero default out, because people entering their hours sometimes don't write over the zero, and we get 80 hours instead of 8. Thank you for your time and trouble.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •