# Thread: sums with criteria in report (97)

1. ## 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.

2. ## 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. ## 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.

4. ## 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. ## 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.

6. ## Re: sums with criteria in report (97)

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

(no is between [daterecorded] and Between)

7. ## 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. ## Re: sums with criteria in report (97)

thanks, that does answer the first question.

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

9. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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 Last

#### Posting Permissions

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