Prev: Allen Browne's Has the Rcd been Printed - Taking it further
Next: Merge Fields into one in Access Report
From: Joel_123 via AccessMonster.com on 1 Jun 2010 00:52
Hello,

I am having a problem with a report. I am trying to print out paystubs for
employees over a given time period. I have a start up form in which the user
types in the “StartDate” and the “EndDate” of the pay period which is 2 weeks.
I need to be able to calculate the regular/overtime hours for each week so I
created two queries, “qryPayrollWeek1” and “qryPayrollWeek2”. Each of these
queries contains the same fields:
Operator/DateWorked/HoursWorked/RegularHours(Calculated Field)/OvertimeHours
(Calculated Field). Both of these queries work great. When I type in a
StartDate and EndDate, I get a record for each day for each employee within
the date range with all of the correct information.

I now want to produce a pay stub for each employee through a report. I built
a report and have 2 subreports (one referring to week 1 and one referring to
week 2). I have tried to have various tables/queries as the main report but
I keep getting asked for the Start/End dates over and over (I am assuming for
each record in each query as well as each record in the main part of the
report). This produces a report that is pages long. I do understand
Parent/Child fields and I have tried various combinations of what I think
would relate the main report with each subreport but no matter what
table/query I try and have as the main report, the same thing happens.

I am just wondering if I need to build a new query for the main part of the
report (and try ans SELECT DISTINCT for each employee within both payroll
queries) or if I should just somehow link back to the table which stores the
individual employee information (tblEmployees). The field “Operator” is not
the primary key but is found in tblEmployees as well as both the payroll
queries. I tried this once before already with no luck but maybe I am
looking at this the wrong way. Any help would be greatly appreciated.
Thanks in advance.

Joel

--
Message posted via http://www.accessmonster.com

From: Marshall Barton on 1 Jun 2010 09:51
Joel_123 via AccessMonster.com wrote:
>I am having a problem with a report. I am trying to print out paystubs for
>employees over a given time period. I have a start up form in which the user
>types in the �StartDate� and the �EndDate� of the pay period which is 2 weeks.
>I need to be able to calculate the regular/overtime hours for each week so I
>created two queries, �qryPayrollWeek1� and �qryPayrollWeek2�. Each of these
>queries contains the same fields:
>Operator/DateWorked/HoursWorked/RegularHours(Calculated Field)/OvertimeHours
>(Calculated Field). Both of these queries work great. When I type in a
>StartDate and EndDate, I get a record for each day for each employee within
>the date range with all of the correct information.
>
>I now want to produce a pay stub for each employee through a report. I built
>a report and have 2 subreports (one referring to week 1 and one referring to
>week 2). I have tried to have various tables/queries as the main report but
>I keep getting asked for the Start/End dates over and over (I am assuming for
>each record in each query as well as each record in the main part of the
>report). This produces a report that is pages long. I do understand
>Parent/Child fields and I have tried various combinations of what I think
>would relate the main report with each subreport but no matter what
>table/query I try and have as the main report, the same thing happens.
>
>I am just wondering if I need to build a new query for the main part of the
>report (and try ans SELECT DISTINCT for each employee within both payroll
>queries) or if I should just somehow link back to the table which stores the
>individual employee information (tblEmployees). The field �Operator� is not
>the primary key but is found in tblEmployees as well as both the payroll
>queries. I tried this once before already with no luck but maybe I am
>looking at this the wrong way.


It sounds like your main report record source query joins to
the table that contains the data used in the subreport. If
so, then you should remove the subreport table from the main
report's query (the Link Master/Child properties will join
the subreport (data) to the main report records.

--
Marsh
MVP [MS Access]
From: Joel_123 via AccessMonster.com on 1 Jun 2010 20:02
Hi Marshall,

Thanks for the response. Could I UNION the two payroll queries together to
use as the main form query or does that still count as including subreport
information? I have tried using various tables and creating queries but I
cant seem to get it to work.

On my report, I have grouping turned on and I am grouping on operator on the
main form. The subreports are in the Detail section and are one after the
other with every field listed in my previous post (not all of them are
visible).

Any ideas?

Thanks again for the help.

Joel

--
Message posted via http://www.accessmonster.com

From: Marshall Barton on 1 Jun 2010 22:41
Joel_123 via AccessMonster.com wrote:
>Thanks for the response. Could I UNION the two payroll queries together to
>use as the main form query or does that still count as including subreport
>information? I have tried using various tables and creating queries but I
>cant seem to get it to work.
>
>On my report, I have grouping turned on and I am grouping on operator on the
>main form. The subreports are in the Detail section and are one after the
>other with every field listed in my previous post (not all of them are
>visible).


Instead of using oarameter prompts in the query, you xould
use a form with text boxes where users can enter the start
and end dates. Then the query parameters would look like:
Forms!theform.starttextbox
Forms!theform.endtextbox
This way the queries know how to get each parameter's value
without asking for it.

I guess I don't understand what the subreport is doing.
Maybe it should be in the operator group footer instead of
the detail section.

--
Marsh
MVP [MS Access]
From: Joel_123 via AccessMonster.com on 2 Jun 2010 00:15
I use the Start/End Date fields from the Forms in the criteria section of the
query (ie) under the DateWorked field, I have "Between [Forms]![frmPayroll]!
[StartDate] and [Forms]![frmPayroll]![EndDate]". All I am trying to
accomplish is to print off a piece of paper for each person that shows Week
One broken down for each individual day showing the regular/overtime hours
and then the same for Week 2. The reason I kept these two queries seperate
is because I need to be able to total the hours for each week seperately. I
was just trying to figure out a way so that the Operator shows up on the Main
Form and then the two subreports simply return the DateWorked, RegularHours,
and OvertimeHours for that particular operator. I also tried creating a new
report based on the WeekOne Query and adding the Week 2 query as a subreport
(so to only have one subreport) but it produces the same results.

I am wondering if I have over complicated this?

--
Message posted via http://www.accessmonster.com

 |  Next  |  Last
Pages: 1 2
Prev: Allen Browne's Has the Rcd been Printed - Taking it further
Next: Merge Fields into one in Access Report