Re: subreport/report total question

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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]

--
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: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