Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004
    Posts
    34

    Unanswered: Calculate total hours with criteria

    I have a table that collects data about time taken off fields like UserID,RequestType,Hours off. Depending on the request type I want to get a sum and then do calculation to against total hours available and return a balance. There are 5 request types, 3 of them that I want to have added together for my calculation. The problem is that in my query I get sum for each type and can't figure how to have sum for all that meet criteria. Any ideas?

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    You can add a field to your types that is a category for each type, then group by the category. Or you can accomplish the same thing with a calculated field, like:

    Category:Iff([Type] In ("Vacation","Personal","Family Emergency"),"Paid Time","Unpaid Time")


    These are just examples, your data and types will be different. But basically this line converts your Types into one of two categories Paid Time and Unpaid Time. Then you will drop the type field in a second query and group by the Category field and sum based on the category field.

  3. #3
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154

    Talking

    you can add multiple crieteria in your query...

    try on the criteria line;

    "Criteria 1" OR "Criteria 2" OR "Criteria 3"

    and this will filter all 3 criteria and then do the sums!

    hope this helps!

    Dan
    sometimes simple is best.... and i'm just a simple fellow.

Posting Permissions

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