Can you summarize a Report footer field based on subtotal footer field 1

[shanl]

I can't seem to find the problem and this seems like something you should be able to do but I'm obviously violating some rule or don't have the right syntax since it's not working.

I have set up a text box in the subtotal footer called Hrsworked. The Control Source for this field looks like this and it works fine:
Hrsworked =IIf([pthrssum]>0,[pthrssum],[reghrs])

I have two other textboxes in this subtotal that calculate pthrssum and reghrs and they are working fine.

In my Report total I want to total up all the hrsworked from all the subtotal footers so I have coded it as such:

Regtot =Sum([Hrsworked])

Instead of summing up the hours of Hrsworked from my Subtotal footers, It prompts me for the parameter Hrsworked when I try and execute the report. I've checked spelling and its the same. What am I doing wrong?

Shan

 

[Crowley16]

by just putting [Hrsworked], you are telling the report that [Hrsworked] is a column in the query for the report, which isn't the case.

try using the reports!contorls syntax...

--------------------
Procrastinate Now!

 

[dhookom]

You can't sum controls across report sections. You can sum fields/expressions based on the report's fields.
Try:
=Sum(IIf([pthrssum]>0,[pthrssum],[reghrs]))


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[shanl]

Crowley16,
I didn't realize the only time you can use the [ ] around a field name is if it's a column from your query. I have brackets in my subtotal line that seem to be working o.k. and are around field names in some of my total calculations. I'll double check. I'm afraid you've given me more credit than my experience holds when you suggested to use Reports!control syntax. Can you give me an example of what Reports!control syntax looks like? I'm really new to Access.

Duane,
Your tip is getting me a little closer but I'm not there yet. I didn't realize you could do a =sum(iif combination so am trying to get that to work. Unfortunately I'm finding that none of my references in my report section will allow me to reference other fields that I've created in the Report footer section like I was able to do in my subtotal footer. Now I get a prompt for those fields. Must I recalculate every single calculation in every report footer field?

I'm trying to use the =Sum(Iif( logic and while I don't get prompted anymore it's giving me numbers that are not accurate so most likely I have something wrong or perhaps teh sequence of how it does the math is messing me up. So now my syntax looks like this

=Sum(IIf([parttimehrs]>0,([parttimehrs]-(([hhr]/60)-([Comp]/60)-([ill]/60)-([Vhr]/60)-([pH]/60)-([WC]/60)-([Jury]/60))),(80-(([hhr]/60)-([Comp]/60)-([ill]/60)-([Vhr]/60)-([pH]/60)-([WC]/60)-([Jury]/60)))))

 

[dhookom]

Folks,
The only time you need []s is when your object (field, table, form,...) name contains spaces or other ugly characters that shouldn't be there. Access will often help you by bracketing stuff that doesn't necessarily need to be bracketed.

I don't understand what Crowley16 means by "reports!contorls syntax" and I doubt there is a solution down that path.

To simplify your report calcs, you might want to create a calculated column in your report's record source like:
SomeHrs: IIf([parttimehrs]>0,([parttimehrs]-(([hhr]/60)-([Comp]/60)-([ill]/60)-([Vhr]/60)-([pH]/60)-([WC]/60)-([Jury]/60))),(80-(([hhr]/60)-([Comp]/60)-([ill]/60)-([Vhr]/60)-([pH]/60)-([WC]/60)-([Jury]/60))))

I don't know if this syntax is correct. I would think you could just add/subtract the fields and then divide by 60.

It also looks like you have an issue with normalization. I would expect to see a field of "TimeType" with values like "Ill", "WC", "Comp", "Jury",...

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[shanl]

Duane,
For new users like myself I can't tell you how much I appreciate the explanation on why things work the way they do and helpful insights as to how Access processes things. (e.g. []). I had always wondered and coulnd't find it anywhere so was never sure when you do and when you don't use []. Thanks.
As for my Report footer line, I tried calculating totals in my report footer line to simplify some of the code as you had suggested to be referenced in other fields in my Report footer but I kept getting prompted for the fields when I would try and create the report. I decided to go into my subtotal line and create invisible fields that would calculate a running total overall that seem to work fine when referenced in my report footer.

Based on the problems I had, Is it not possible to reference calculated fields in the Report footer with other fields in the Report footer?

Side note - I really appreciate all the help this forum provides. Everyone is very professional and truly a great resource who gives of their time to help others that aren't (Yet) at their expertise level. Thanks for that. You are truly appreciated.
Shan

 

[dhookom]

As I stated earlier, you can't sum or otherwise aggregate "controls" across report sections. You should be able to reference values of other controls within the same section. For instance if you have these controls in your report footer:
Name: txtSumCost
Control Source: =Sum([Qty] * [UnitCost])

Name: txtRebate
Control Source: [Rebate]

You should be able to create a text box:
Name: txtCostMinusRebate
Control Source:=txtSumCost - txtRebate

I would probably use:
Name: txtCostMinusRebate
Control Source:=Sum([Qty] * [UnitCost]) - [Rebate]

I'm not sure why you needed to use a running total.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[shanl]

The reason that the running total worked for me was in regards to how I'm calculating full time people's hours. I don't know the number of Regular employees that I'm processing and since I don't have a field (by choice) that I'm using to sum up a person's regular worked hours, it worked perfect to catch those totals at Subtotal time and sum them that way. Every full time person gets 80 hrs.
I know I could put a counter in there to calculate number of employees and multiply by 80 and then subtract from all the Leaves at Report footer time, but I've already done those calcs at Subtotal time and this was much simpler to have that running total in place. Since I was calculating the one total, it seemed logical to use all the other Leave totals as running totals too and use them in my total line. While your example seems like it should work above I could not get it to work so there must have been something wrong with my syntax. Is there a downside to my using running totals that I'm not seeing?

 

[dhookom]

I find the running totals a little more difficult to implement. If you have the correct totals then go with it.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]