Trying to total a calculated field form a group footer in another group footer

[Deputy91]

I am trying to total the FLSA Cash for all of the pay weeks for one employee (group) above. FLSA Cash is a calculated fields using AVG([Work Week]), SUM([OTCASH], SUM([OTCOMP]), SUM([LEAVE]) and AVG([Hourly])




I believe that I may have to setup a query to do the calculation, but how do I do that with fields from the report?


I also want a grand total for all employees at the end of the report. I think if I get past the first calculated field issue, everything else will fall into place.




the report is grouped by employee then by pay week. HELP!

[Welshgasman]

Crossposted at https://www.access-programmers.co.uk...footer.334490/

[Deputy91]

Yes sorry for the crosspost, I wasn't getting any response here.

[Welshgasman]

Just to let people know. Weekends tend to be quiet.
I would expect a control with sum attributes, it I always need to test it out.

[Deputy91]

Yes, maybe my question should be how do i setup a query to calculate controls in the report, then total them in the footer

[Welshgasman]

Try the wizard. That will actually do it for you. Then you can tweak to your hearts content.
NB: Any sort order to the source is ignore by reports. It si done within the report.

Here is one I created quickly with the wizard on my data of one of my dbs.

[June7]

You said "FLSA Cash" - should that be "FLSA Cost"?

Not understanding your difficulty.
Using same aggregate functions calc in employee and report footers should accomplish this.

[Deputy91]

SOLVED, thank you - A member walked me thorough the solution.

[Welshgasman]

SOLVED, thank you - A member walked me thorough the solution.

So please pass on your solution.
That is what, after all, these forums are for, not just to take and leave.
The whole idea is to share solutions, not keep them to yourself, even if you work them out yourself.

I suspect I also know as to who is doing that 'private help'. That helps nobody else, as they cannot see the solution, but the helper thinks he is the bees knees.

[mike60smart]

Hi Gasman
The OP did not want to upload his database to the Forum and also had no idea on how to create a query .
All I did was correct his table fields which had been set as text Data Currency.

His original table data shown.

I then showed him how to change the Record Source of the Report to a Select Statement as shown below:-

SELECT
    [CSEA Data 2016 - Time Type Columns].[Event Date],
    [CSEA Data 2016 - Time Type Columns].[Employee ID],
    [CSEA Data 2016 - Time Type Columns].[Employee Name],
    [CSEA Data 2016 - Time Type Columns].[Title Description],
    [CSEA Data 2016 - Time Type Columns].[Pay Week],
    [CSEA Data 2016 - Time Type Columns].TRC,
    [CSEA Data 2016 - Time Type Columns].LEAVE,
    [CSEA Data 2016 - Time Type Columns].OTCOMP,
    [CSEA Data 2016 - Time Type Columns].OTCASH,
    [CSEA Data 2016 - Time Type Columns].[Work Week],
    [CSEA Data 2016 - Time Type Columns].[HOURLY       ],
    (Nz ([Work week], 0) -40) - Nz ([leave], 0) AS D,
    IIf(
        [Work Week] < 40,
        Nz ([Leave], 0) + Nz ([OTCash], 0) + Nz ([OTComp], 0),
        0
    ) AS LessThan40,
    IIf(
        [Work Week] > 40,
        Nz ([Leave], 0) + Nz ([OTCash], 0) + Nz ([OTComp], 0),
        0
    ) AS GreaterThan40,
    IIf([OTCASH] < [D], [OTCASH], [D]) AS Hrs,
    [Hrs] * [Hourly] * 1.5 AS [FLSA Costs]
FROM
    [CSEA Data 2016 - Time Type Columns];

He was then able to get the Total of the "FLSA Costs" as required in the Footer of the Report.

[Welshgasman]

OMG what a table naming convention.
Thanks for explaining, it could help others, you never know.

NB: Only one table?, you can exclude it's name for the fields.

[June7]

Query builder will add table name prefix to each field as shown.

I would advise not to use spaces nor special characters/punctuation in naming convention.

[Welshgasman]

Question.
On my phone, but what if the work week is 40?

[mike60smart]

Hi Gasman
Good spot. I was just going with the OP's calculations and had missed that one.