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

0
Joel_123
6/1/2010 4:52:04 AM
access.reports 4434 articles. 0 followers. Follow

6 Replies
5929 Views

Similar Articles

[PageSpeed] 23

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]
0
Marshall
6/1/2010 1:51:10 PM
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

0
Joel_123
6/2/2010 12:02:22 AM
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]
0
Marshall
6/2/2010 2:41:12 AM
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

0
Joel_123
6/2/2010 4:15:16 AM
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]
0
Marshall
6/2/2010 9:28:37 AM
Tried your second solution.  Worked perfectly.  Thanks for the help.  It's
greatly appreciated.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/201006/1

0
Joel_123
6/3/2010 3:40:01 AM
Reply:

Similar Artilces:

Total from SubReport Data But Not in SubReport
Hello, This is probably pretty simple, but I have created a report where the main display is a subreport of items ordered by the customer order and price. I would like to total the prices in the main report, but not from a total on the subreport. The price field is called Expr5. I have tried =Sum([Expr5]), but this gives and #Error I can't figure out what you are asking. If you don't want the total from the subreport, what do you want? Values displayed in a report must come from somewhere. "Expr5" means nothing to any of us other than you haven't taken the time ...

Keep Focus
The code below alerts the user that some fields are blank. It works until the last line!!! How can I make it keep the form open an go to the first blank field, then the second etc..(I'll be adding more)??? Private Sub Form_Close() If (IsNull(Me.FirstName)) Or (IsNull(Me.LastName)) Then If MsgBox("Do you want to Edit Member Information now?", vbQuestion & vbYesNo, "Question") = vbYes Then (Me.???) ???.SetFocus On Thu, 31 Jan 2008 09:16:00 -0800, Dan @BCBS wrote: > The code below alerts the user that some fields are blank. > It works until t...

Keep cell selection
Hello NG In a excel sheet i have 5 buttons. Colored red, green, blue, yellow and purple. The user must select some celles on the sheet and then click on a button. The click should call a VBA code and place the button backcolor in the selected cells. My problem is that when the user clicks the button, the selected cells disappears. Is there a way to hold the selection until the user has clicked a button? Kind regards Johnny E. Jensen If you use CommandButton's from the Control Toolbox toolbar, then you can set its TakeFocusOnClick property to False... do this ...

Deleting an email account, but keeping the emails
Can I delete an email account so I can reset it up, and still retain all the emails I have saved in there to date? Yes. Deleting an account in Windows Mail does not delete any emails. --=20 Gary VanderMolen, Microsoft MVP (Mail) http://mvp.support.microsoft.com/default.aspx/profile/vandermolen "c_angler" <cangler@discussions.microsoft.com> wrote in message = news:3E3BB56B-59FB-43ED-BD65-316C2B8F5B3C@microsoft.com... > Can I delete an email account so I can reset it up, and still retain = all the=20 > emails I have saved in there to date? > Yes. ...

Page break before subreport if subreport has data
How can I create a page break before the subreport if and only if the subreport has data? Some records will have no data in the subreport, and I don't want an unnecessary page break in those instances. Thank you, Kathy Add a page break control and some code like: Me.PgBrkName.Visible = Me.subrptName.Report.HasData -- Duane Hookom Microsoft Access MVP "Kathy Webster" wrote: > How can I create a page break before the subreport if and only if the > subreport has data? > Some records will have no data in the subreport, and I don't want an > unnecessary pag...

Reports and SubReports Spacing
I have an AC2003 report. That main report (Main) has 4 subreport controls on it (Sub1-4). Each of the reports associated with the subreport controls may or may not have data in the corresponding table to report on. The Main report has the following layout: Main - Sub1 - Sub 2 - Sub 3 - Sub 4 When Main runs, if one of the subreports is blank, the area for that subreport is blank (expected), but if any of the subsequent subreports do have data, those subreports display in their position. In the following example, subreport 2 has no data Example ...

group sections in a report to print or generate subreport
Hello all, Is it possible to generate a report (or a subreport) that will allow a separate report for each server (for grouping and printing purposes) if I have many server names (with DB, Apps, etc.) in one huge report. Maybe using a button on the large, main report (with all server names) to print the server name on the current page along with the server's information? Or instead of printing maybe there could be another button that could generate a subreport of that current page's server and it's information? My question is: After generating a report, how does one go about gr...

Something to help me keep track of formulas
Is there an Add-In or some third party software that can help me manage my formulas. They are getting very long and I can not figure out what goes where. I am looking for something free if possible. THanks. Here is an example of my formula that's too long. =IF(A12="Total ",SUM($I$11:$I11),IF(A12="","",IF(ISBLANK('PASTE ABCone'!F12),IF(((VLOOKUP(A12,'PASTE ABCone'!$A$11:$I$300,9,FALSE))*(VLOOKUP(A12,'%'!$A$9:$E$505,5,FALSE)))=0,"",ROUND((VLOOKUP(A12,'PASTE ABCone'!$A$11:$I$300,9,FALSE))*(VLOOKUP(A12,'%'...

news option keeps disappearing from Outlook 2004
Outlook and Outlook Express on same machine. Can't make Outlook the default newsreader in Internet Options. News option keeps disappearing from Outlook and keeps reverting to Outlook Express. Solution? Assuming you mean Outlook 2003: To get the News command back, go back to the Programs tab in Control Panel | Internet Options and change the default newsreader to Outlook Express. If OE prompts you to make it your default newsreader the next time you launch it, clear the "Always perform this check..." box and then click the No button. -- Russ Valentine [MVP-Outlook] "...

positioning a subreport
hello I have a subreport in my main report's Page Footer. The subreport shows a single column with from 1 to 5 rows. The subreport has a solid hairline border that can grow. I want the bottom of this border to always be 0.5" above the bottom of the main report's page. (When there are more rows, I want the subreport to grow UP, not DOWN). Can this be done? Thanks --- cinnie cinnie wrote: >I have a subreport in my main report's Page Footer. The subreport shows a >single column with from 1 to 5 rows. The subreport has a solid hairline >borde...

keep changes are made on datasource.
Hello. I have DataTable object. Changes are made on the datatable, but I need sometimes to refresh the datatable contents (I have master-detail form, and when the master record is changed, there are new elements on the datatable). So, I need to keep changes that were made on the details table. How can I keep the old changes, before any commit to database ? Need an example, please. Thanks :) Sorry. Problem solved ! I have to use the same dataAdapter from the time I initialize the dataset through its whole lifetime. Thanks :) ...

Outlook keeps making itself Default
Hi, Everytime I start Outlook it takes over as default for email and addresses even though I have that option unchecked in outlook, and in IE I set OE Express as default, yet each time I launch outlook it takes over as Default, any ideas TIA Wayne ...

multiple counts on one report
Hi All, I have a report that needs to total 2 different things: Bed Days, and Clients Served. Each of these is broken down by Contract: ASOC or SACPA. I have a text box in the "Contract" Footer that counts how many clients are under each Contract. Name: Contract Count Control Source: =1 I have a text box in the Report Footer that totals the Clients Served (all clients under every Contract). Name: Clients Served Count Control Source: =Count(*) I need the same report to total: How many days each client (under each Contract) was in residence during the report period (Bed Days), and...

How do I Run Outlook and keep it on the hard disk
I had another hard drive installed on the computer and had Office 2000 installed on it but I do not appear to have Outlook. How can I get it onto the hard drive and get it to stay there? Mike <Mike@discussions.microsoft.com> wrote: > I had another hard drive installed on the computer and had Office 2000 > installed on it but I do not appear to have Outlook. How can I get it > onto the hard drive and get it to stay there? Can you be more precise? If, for example, you hae another disk added to your PC and the new disk was made the C drive, then it's no surprise. If, i...

Keep all Tect Boxes the same size (depth)
Hi using pub 2007 I am creating an A5 double sided recipe book Each page will have 1, 2, 3 or 4 recipes I have created a text box manually around each recipe name from margin to margin until it looks right & fill the box with 40% black. All text fonts are the same size How would I ensure that all my text boxes were the same depth ? -- Martin ���� After you create your text box, right-click the box, format text box, check *Apply settings to new text boxes". Of course you can copy/paste. Place your cursor where you want the paste, right-click, click paste. You may have some probl...

Subreport filter
Hi, I'm working with Access 2003, and is having problems with setting the filter for a subreport I have. Here's how my report works. For each student in a class specified by the user, it would print out a page consisting of the student's information for the specified class (i.e. marks, homework completion etc.), as well as a list of other classes the student is in. I have it set up so that the subreport is the list of other classes the student is in. What I have a problem is how to filter out the class that was specified by the user. [So if Student A is in math, english and chemi...

drag a formula downa column keeping one value the same?
I need to drag a formula down a column, but keep one value constant (when I drag the formula down, the values in the formula change according to the row, but I need to keep one constant so each cell's formula refers to one specific cell.) Ex. - =IF(L30>40000,L1*.60,=IF(L30<20000,L1*.5,=IF(L30<40000,L1*.55,0))) I need to keep the L30 values the same, while the L1 changes for each row, but when I drag this formula down the column, the L30 changes. Thanks! Change your L30 to a absolute reference $L$30 -- If this helps, please remember to click yes. "...

Same SubReport
I want to use the same Sub-Report on two different main reports,however; I don't know how to reference the main report. I don't want to build two seperate Sub-Reports. =[Reports]![rptCashOutServer].[rptCashOutPayments]![PayName] & " " & "Ttl" =[Reports]![rptCashOutCashier].[rptCashOutPayments]![PayName] & " " & "Ttl" New Code? =[Reports]![??????????].[rptCashOutPayments]![PayName] & " " & "Ttl" Any help appreciated, Thanks DS DS wrote: >I want to use the same Sub-Report on two different m...

Windows Installer keeps running
Roxio Media Manager keeps trying to install on my desktop. The installer is looking for the CD which of course I do not have. I tried the various MS support center solutions including disabling the installer via Services. This did not work. I tried to roll back windows to the last known date before I had the problem. This did not work either. I tried the next step which was to install Installer Cleanup Utility. When trying to run the installation I can't install msicuu2.exe because I get "Error 1500. Another installation is in progress". No other installat...

Report footer values equal to subreport footer values
I have 3 crosstab subreports (BarChartData1, 2 & 3), each having a row of text boxes (Tot7 thru Tot67) in the report footer which display column totals generated through code in each report and each report is nested separately in the report footer of a main report. Now, included in the report footer of the main report, below the 3 subreports, are a 3 rows of textboxes, each row should be should be an exact representation of the 'totals' row in each subreport. Here's my problem...I tried setting up the control source for the very first textbox to =[BarChartData1].[Report]![Tot7]...

How do I keep the compose email text normal size?
Please Help! When composing a new email, I want the text to appear the same size as it will appear to the recipient of the email. Currently it is way larger while I am composing. I am composing on a background that the text needs to fit properly into certain areas. I don't want top use the text box. Thank you. JoanInGA <JoanInGA@discussions.microsoft.com> wrote: > Please Help! When composing a new email, I want the text to appear > the same size as it will appear to the recipient of the email. > Currently it is way larger while I am composing. I am composing on...

How to keep source data for the Excel Charts constant ... Please H
Hi, I am creating excel charts using C#. The range for creating chart is from Row1 to Row400. After the chart is created, first 100 rows need to be deleted. When I delete the first 100 rows (Row1 to Row100), the data range for chart reduces to 300 (Row1 to Row300). I dont want this to happen. I want that the source data range should always remain constant i.e. from Row1 to Row400. Code Snippet xlBookScript = (Excel.Workbook)xlBooks.Open(sDestinationFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,...

Password prompt keeps popping up.
My network connection password request keeps comming up every minute. The 'test settings' feature says that incomming mail is not happening because of an incorrect password. New incomming mail is being turned away. What is hapening???? Stuts wrote: > My network connection password request keeps comming up every minute. The > 'test settings' feature says that incomming mail is not happening because of > an incorrect password. New incomming mail is being turned away. What is > hapening???? A "network password" is completely different than...

Desperate!!! Subreport Help.
I have never used subreports before and I am having a very hard time here. What I need to do is this. I have 5 tables and they all have Project Number and Task Number in them. The Tables are: SITE LIST Inscope Table Outofscope Table Actuals Commits I want to select Project Name,Project Number, Task Number, Site ID as the header of the report and also the data for the main form. Then I want to create 4 subreports using the other tables but I want them to only display their selection based on the Project Number and Task Number in the main form (i.e. the SITE LIST table). How do I do this...

How do I link cells and keep the formating in excel?
I don't know how to link a cell and the formating from one cell to another. I am trying to link a cell that has text with a superscript (like k^4). Currently, the linked cell would just show it as text (i.e. k4). Can I create a smart tag for this? Do I need to make a macro to do this? Can I make an autocorrect for this? Please help!! "thanksforthehelp" <thanksforthehelp@discussions.microsoft.com> wrote in message news:9009A437-DF43-443A-9075-0FB4C2BB9460@microsoft.com... > I don't know how to link a cell and the formating from one cell to another. > I ...