Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    sums with criteria in report (97)

    hi all
    i have what seems to be a simple question... i have a query that displays time worked on specific dates, one entry per date. i would like to create a report that displays the sum of the time worked for each the year to date and the total of the previous week. i haven't figured out how to use the sum function with criteria yet. any help would be appreciated.

    thanks in advance

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: sums with criteria in report (97)

    I'm fairly sure that there is no criteria associated with Sum, however why not use the where clause in a query, or, in your case where you have multiple criteria requirements you could use the DSum's in a query with their own criteria.
    HTH
    Pat

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: sums with criteria in report (97)

    You don't use a criteria with Sum. Sum in a report totals a control over a group. Sum in a query totals a field over a group. Use the Sorting and Grouping to create groups in your report and put the Sum equations into the appropriate footers.
    Charlotte

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: sums with criteria in report (97)

    There's no such thing as a "conditional sum" aggregate function in Access like SUMIF and COUNTIF in Excel. You have to do it the other way around, create a conditional expression with SUM function inserted. Example: In a totals query, create a calculated field like this:
    <pre>SUBTOTAL1: Sum(IIf([FLD_A]="ABC",[FLD_B],0)) </pre>

    where FLD_B is the field you want to be summed, FLD_A = (whatever) is the condition to be evaluated. You can use same expression in a report group or report footer by setting textbox control source to:
    <pre>=Sum(IIf([FLD_A]="ABC",[FLD_B],0)) </pre>

    You would use similar expression if you want "conditional count" totals, only you'd replace the field name to be summed with a "1":
    <pre>COUNT1: Sum(IIf([FLD_A]="ABC",1,0)) </pre>

    This will work in a report group footer as well. You can add as many calculated columns or controls as needed. Not sure how you are calculating time values. Keep in mind in a query you can create calculated field based on other calculated fields using the field alias, example, if you have 2 "conditional sum" columns SUM1 and SUM2, you can simply sum these in a 3rd column with expression SUM3: SUM1 + SUM2, while you cannot do that with calculated controls in a report.
    HTH

  5. #5
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sums with criteria in report (97)

    thanks for your help. i have 2 questions..

    how do you use a "between ___ and ___" in this function (i am comparing dates)? access tells me i have invalid syntax or the wrong number of arguments. i am trying to use something along the lines of

    YTDSubtotal: Sum(IIf([daterecorded] is between #1/1/2002# and #12/1/2002#),[employeetime],0))

    but it, along with my other attempts are not working. i also would like to use now() in place of the second date, but it doesn't like that either.

    the next question is:

    How come i can only display this field in the query and none of the others? i need the rest of the fields as well.

    thanks for your help

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: sums with criteria in report (97)

    To answer your first question: use

    Sum(IIf([daterecorded] Between #1/1/2002# And Date(),[employeetime],0))

    (no is between [daterecorded] and Between)

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: sums with criteria in report (97)

    About the second question: there is no reason you can't use several fields in the query once you've solved the first question, for instance

    SELECT Sum(IIf([daterecorded] Between #1/1/2002# And Date(),[employeetime],0)) AS YTDSubtotal, Sum(IIf([daterecorded] Between Date()-7 And Date(),[employeetime],0)) AS LastWeekSubtotal FROM tblWhatever;

  8. #8
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sums with criteria in report (97)

    thanks, that does answer the first question.

    now, just waiting for someone to answer the last question.....

    thanks for your help

  9. #9
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sums with criteria in report (97)

    if i only have 2 fields (one calculating year to date times, and the other calculating last week times) everything works fine. if i drag a field (in this case "EmployeeTime') from the table to the query design grid, it gives me the error;

    "You tried to execute a query that doesn't include the specified expression 'EmployeeTime' as part of an aggregate function.

    the error occurs when i try to execute the query. any insight you might have would be appreciated

  10. #10
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: sums with criteria in report (97)

    That's the error you normally get if you have a totals query and try to sort on a field used for the WHERE criteria. If you want to sort on this field you will have to add it to query design grid twice, once with GROUP BY (check "Show"), second with WHERE (don't check "Show") and specified criteria. You can then specify sort order in either column for this field. Otherwise remove sort order for the WHERE criteria field.
    Hope this makes some sense.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: sums with criteria in report (97)

    Using the Sum function makes your query into a totals query. In a totals query, each field must be either a "group by" field, a criteria field ("where") or use one of the aggregate functions (sum, average, max, first, ...). Otherwise, the query engine doesn't know what to do with the field.

    If you want to display both individual times AND the totals in one query, create two separate queries first, one with the individual fields and the other with the totals. The latter will return just one row.
    Then, add both queries to a new query. There is no need to join them. Drag the desired fields into the grid.

  12. #12
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sums with criteria in report (97)

    hey, thanks
    i don't completely understand all of the first paragraph, but the second paragraph seems to work. thanks for all your help.

  13. #13
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: sums with criteria in report (97)

    @ Mark, this is again one of these wonderful Lounge posts... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>.
    But... I have an additional question...

    I want to add to my footer a "conditional sum" for each value occurring in a specific field in my report. These values however may vary (and also the number of them and so the number of total controls!).
    Is there any other, alternative approach I could use here or can I modify the method above (which requires predefined values) to solve my problem?

    Example report:
    - Data = State + Client + Year + Amount (grouped and/or sorted on client - client - year)
    - State subtotal = total amount for this state for Year XXXX (sum of all clients' amounts) = 'conditional sum' => how to...?
    - State total = total amount for this state (sum of all clients' & years' amounts) = 'regular' sum = OK

    Thanks for any suggestions that you might have!
    Hasse

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: sums with criteria in report (97)

    If your report is grouped on client, then on year, the group footer for a specific year will occur for each client that has data for that year. Where would you want the subtotal for all clients in a year to appear? In the year group footer doesn't make sense, since it will be repeated, nor in the client footer, since you want to sum over all clients, and in the report footer you don't have the individual years. So what exactly are you ttrying to accomplish?

  15. #15
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: sums with criteria in report (97)

    Woops... typo! Some clarification...

    Wrong text:
    <hr>Example report:
    - Data = State + Client + Year + Amount (grouped and/or sorted on <font color=red>client</font color=red> - client - year)
    - State subtotal = total amount for this state for Year XXXX (sum of all clients' amounts) = 'conditional sum' => how to...?
    - State total = total amount for this state (sum of all clients' & years' amounts) = 'regular' sum = OK<hr>
    Right & clarified text:
    <hr>Example report:
    - Data = State + Client + Year + Amount (grouped and/or sorted on <font color=448800>State</font color=448800> - Client - Year)
    - State subtotal <font color=448800>(State footer)</font color=448800> = total amount for this state for Year XXXX (sum of all clients' amounts) = 'conditional sum' => how to...?
    - State total <font color=448800>(State footer)</font color=448800> = total amount for this state (sum of all clients' & years' amounts) = 'regular' sum = OK<hr>
    So... I want the (sub)subtotal for all clients for a given year in state X to appear in the State footer section, before/together with the State (sub)total.

Page 1 of 2 12 LastLast

Posting Permissions

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