Sum If another column meets criteria

[dvs]

Hi,

I have two columns in my table:

1) Category - Installation, Operation, Office, Rework
2) Hours - Number of hours for each task

I am trying to generate a report that summarizes the number of hours spent under Installation, Operation, Office, Rework...

So at the report / page footer, I would like:

Hours worked on:
Installation = 45
Operation = 160
Office = 35
Rework = 103

Note that I DO NOT want to include these fields in the 'Details' portion of the report (i.e. pages and pages of data, just to get a summary at the bottom).

Thanks for your help - I tried searching Iif / sum already. (I know the Excel equiv. is SumIf()...)

dVs

 

[Rich]

=Sum(Iif([Category] ="Installation",[Hours],0) etc.,

 

[Pat Hartman]

create a query that summarizes the data:

Select Category, Sum(HoursWorked) As SumHours
From YourTable
Group By Category;

Create a report that uses the above totals query as its recordsource. Then place the report as a subreport in the original report's report footer section.

 

[dvs]

Rich and Pat,

Thanks for your help!

Rich> The Sum/Iif combo does not work. I am using Access97 - does this matter? I seem to have the ' or " syntax wrong...

I have tried...
=Sum(Iif([Category] ="Installation",[Hours],0) <--your posting
=Sum(Iif("[Category] =Installation",[Hours],0)
=Sum(Iif("[Category] ='Installation'",[Hours],0) ...etc


Pat> What you gave me works. How do I put "another total" in the report footer, basically as:

TotalRegular = (Installation + Operation)

Would I use another subform?

I have an Event Procedure in my Category header, that On Format, sums up the [Hours] value in a TotalRegular variable, IF Category = Installation Or Operation. In the footer, I have a textbox txtRegular. On Format of the footer, txtRegular.Value = TotalRegular.

My sum is twice what it should be - because my hours loop thru twice?!

Your further help is appreciated - I can see the light at the end of the tunnel!

Cheers,
dVs

 

[Pat Hartman]

I like to avoid this type of "hard-coding" whenever possible. Somehow it always seems to grow and get more complicates. One possible solution is to create a table of catetgoris. In the category table include a group column. Join to this category table in your subreport's query to obtain the group column. Use the group column as the primary sort sequence and add a group footer to sum the values.