A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Subreport Keeps Repeating Itself



 
 
Thread Tools Display Modes
  #1  
Old June 1st, 2010, 05:52 AM posted to microsoft.public.access.reports
Joel_123 via AccessMonster.com
external usenet poster
 
Posts: 5
Default Subreport Keeps Repeating Itself

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

  #2  
Old June 1st, 2010, 02:51 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Subreport Keeps Repeating Itself

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]
  #3  
Old June 2nd, 2010, 01:02 AM posted to microsoft.public.access.reports
Joel_123 via AccessMonster.com
external usenet poster
 
Posts: 5
Default Subreport Keeps Repeating Itself

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

  #4  
Old June 2nd, 2010, 03:41 AM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Subreport Keeps Repeating Itself

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]
  #5  
Old June 2nd, 2010, 05:15 AM posted to microsoft.public.access.reports
Joel_123 via AccessMonster.com
external usenet poster
 
Posts: 5
Default Subreport Keeps Repeating Itself

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

  #6  
Old June 2nd, 2010, 10:28 AM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Subreport Keeps Repeating Itself

Joel_123 via AccessMonster.com wrote:

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?



I think my original diagnosis may have been close. If the
main report's only data is the person and date, then the
main report's record source query should only contain the
person table. There would be no grouping since the detail
section would have the person info and the subreports. The
Link Master/Child properties whould have the person id to
link to the subreports fot the person.

OTOH, I don't think you actually need the subreports. If I
understand what you want, I think this would be my preferred
approach. Add a calculated field with the week (1 or 2) to
the main report's record source query using an expression
like:
WeekNum: DateDiff("ww", Forms!frmPayroll!StartDate,
datefield) + 1

Then add a group on the WeekNum field and total each week in
the WeekNum group footer section with Sum expressions. The
grand total for both weeks would be the same expressions in
the person group footer.

--
Marsh
MVP [MS Access]
  #7  
Old June 3rd, 2010, 04:40 AM posted to microsoft.public.access.reports
Joel_123 via AccessMonster.com
external usenet poster
 
Posts: 5
Default Subreport Keeps Repeating Itself

Tried your second solution. Worked perfectly. Thanks for the help. It's
greatly appreciated.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/201006/1

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:18 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.