Re: subreport/report total question

The core idea is to accumulate each total at each level. Something like this:

1. In the subreport, move your DayTotal text box into the Report Footer section, so it collects all values in the subreport. (Set the report footer's Visible property to No if you don't want it to take any space.)

2. In the main report, place a text box in the Detail section (i.e. the same section as the subreport itself), and set these properties:
Format General Number
Visible Yes (I think you want so show this?)
Running Sum No
Name txtSub1Total
ControlSource =IIf([Sub1].Report.HasData, Nz([Sub1].Report.RunningTotal, 0), 0)

3. Add another text box to the Detail section to accumulate the total. Properties
Format General Number
Visible No
Running Sum Over Group
Name txtSub1TotalRS
ControlSource =[txtSub1Total]


4. Add a text box to the Work_Date Footer. Properties:
Format General Number
Visible No
Running Sum Over Group
Name txtSub1WorkDateRS
ControlSource =[txtSub1Total]

5. Add a text box to the Job Footer. Properties:
Format General Number
Visible Yes (I thinkg you want to show this)
Running Sum Over Group
Name txtSub1JobRS
ControlSource =[txtSub1WorkDateRS]

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Shanin" <Shanin@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:EC492192-09A3-4371-91C7-7F30ED76EC98@xxxxxxxxxxxxxxxx
I've tried this, or at least something similar I believe, and I can't get the
job total to work, it just keeps a running total for every thing, it doesn't
break when the job does, and if I switch it to running sum over group, it
only shows the day total. Maybe I have the form set up wrong. Here is the
basic layout and maybe you can point me in the right direction.

Main Report: ActualvsSchedule
Report Header (Report title)
Page Header (headings/titles for data)
Job Header (contains Job and is set to break before each section)
Work_Date Header (contains work_date)
Detail (contains sub-reports Schedulesubreport and Actualsubreport, also
contains text boxes which are set equal to the hour totals in the work_date
footer of the sub-reports and are hidden)
Work_Date Footer (contains 3 text boxes for each sub-report, CN (day hours),
ON (night hours), DayTotal (all hours) which are set equal to those hidden
text boxes)
Job Footer-I've tried many different things here to get an job total, none
have worked so far

Sub-Reports
Report Header (blank)
Page Header (blank)
Detail (contains employee, time-in, time-out, department, hours, pay-type)
Work_Date Footer (contains 3 text boxes to total hours, CN (day), ON
(night), DayTotal)

I have tried adding a job footer in the sub-report as well as the job header
and Work_Date header and adding the 3 text boxes to total the hours by job,
and when opening the sub-report by itself, it will total correctly in the
sub-report, breaking by job. When I go to open in it the actual report, the
job totals always match the Work_Date totals because it gives me a job total
for each day.

I can get the day totals fine in the main report, but it's like it can't
figure out where the job ends since it will either mimic the day totals, or
it will just keep a running sum of everything.

I should note, this pulls off of a union query which combines the dates and
jobs from the schedule table (empschd) and the actual time worked table
(tktimcrd)




"Allen Browne" wrote:

The text box on the main report is fine.
You might want to use something like this:
=IIf([Sub1].[Report].[HasData], Nz([Sub1].[Report].[txtTotal], 0), 0)
Explanation:
http://allenbrowne.com/casu-18.html

If that one is named txt1, you could use a 2nd text box with properties:
Control Source =[txt1]
Running Sum Over All
Visible No
Name txtRS

Then in the Report Footer section, place a text box to show the grand total,
with Control Source of:
=[txtRS]

"Shanin" <Shanin@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:21A38C4D-8601-4678-B3B1-BF82A30DEFA7@xxxxxxxxxxxxxxxx
> I've gone through past questions here and figured out how to get this > to
> work, but I was trying to make it a cleaner report and can't get this > to
> work.
>
> I have a Report with two subreports put in the detail section of the > main
> report. One subreport pulls schedule data, one pulls actual time > worked.
> The main report only pulls the "work_date" and "job", the two > subreports
> are
> linked by these two fields.
>
> On the main report I have the report header, page header, job header, > and
> work_date header. Basically it sorts by job and then by date. I want > the
> total of subreports to be on the main report, and I have done that to > an
> extent.
>
> I have the subreports having a work_date footer and totaling the daily
> hours, it is called "daytotal". In the main report detail section I > have
> a
> field set to =[subreport].Report![daytotal] and set running sum to over
> all
> and I have this field hidden. I have one for each sub-report. For the
> sake
> of this it's named "runningtotal". I can then reference it in the Job
> footer
> of the main report for a total for the job for the date span entered.
>
> What'd I really like to do is get the day total in the main reports
> work_date footer so that the totals from the two subreports are lined > up
> exactly side by side. I can get it to show the day totals if I set the
> hidden field "runningtotal" in the detail section back to not > calculating
> a
> running sum and setting a field there equal to that. If I do this > though,
> I
> can't total by job anymore? Why can't I just reference the text box I
> made
> in the main report work_date footer, say it's named "ScheduleTotal", > and
> put
> in the main reports job footer =Sum([ScheduleTotal])? This doesn't > work
> of
> course, but how can I get a total in the main report job footer if I > move
> the
> day totals to the main reports work_date footer?
>
> I'm sure that sounds rambled, it's clear in my head but I'm sure not on
> paper.
>
> Thanks



.

Relevant Pages

  • Re: I need to do some totals of subreports
    ... I'll address your issues in the reverse order: ... To determine your totals, you'll need to restrict the data in the ... your record selection in your main report, ... The more subreports you have, the slower your report will be. ...
    (microsoft.public.vb.crystal)
  • Re: Repost-Still Suffering with Subreports
    ... so I built a small subreport on the Volunteer ... footer of the Volunteer ... I take that field name and go to the main report and put ... >totals in the subreport in the report footer. ...
    (microsoft.public.access.reports)
  • Re: Repost-Still Suffering with Subreports
    ... totals in the subreport in the report footer. ...
    (microsoft.public.access.reports)
  • Re: Can I add a summary in the report footer to do this...?
    ... Can you create a totals query grouped by BU and base a subreport on it? ... the subreport to the report footer. ... > footer and provides me with the total Lot Qty of parts that were audited. ... > calculations provide me with a calculation of the BU and Plant OQL ...
    (microsoft.public.access.reports)
  • Re: Showing Totals from Subreport on Main Report
    ... to accumulate the totals to show at the end of the main report. ... Your 3 subreports could be in different sections of the main report? ... This requires another text box, with its ControlSource set to the name or your subtotal text box, and Running Sum set to accumulate the total. ...
    (microsoft.public.access.reports)