Reports :: Adding Multiple Subreports On Main Report
Feb 12, 2015
I am relatively new to Access. I am having difficulty summing up totals and bringing them into the main report. To be more specific I have two sup reports called WOExpenseTotalSubreport and WOServiceTotalsubreport. These two subreports are based off of queries that gather the description, price and qty of either services or expenses and then produces a "total" for me on the query.
When I bring these two queries into a report as sub reports I am able to Sum the "total" field in the subreport footer which works fine. What I would like to do is add those two totals together for a grand total on the main report. The two summed fields are called ExpensesSum and ServicesSum. It seems most things I have tried do not produce an expression error but the result is either #error or #name? Here are a few different scenarios I have tried both on the main report details section as well as the footer section.
I have a database (Access 2007) that handles a large number of consultants. Each consultant has a number of skills, has worked in a number of countries, and speak several languages. As contracts come in, I wish to generate a report based on the skill set, language and country/countries that best suits the contract; thereby identifying a group of most suitable consultants. I have designed a report with three sub reports for skills, languages and countries. Each sub report is linked to the main report through Link Master/Child fields using the ConsultantID. That's all fine; I get a report showing all consultants listing all their qualities.
Now, I want to be able to filter the sub reports on the three aspects: Skills; Countries; and Languages.
In the past, for other applications, and with the unfiltered form in preview I have used a popup form with unbound Combo Boxes to generate a string to use in the reports filter. I think I got that technique from Allen Browne, and it has really served me well.
Now, however, I can't use this technique since I can't set the filter for the sub reports. I thought that I might be able to use the Link Master Field with text boxes on the main report, but haven't managed to figure that out.
I'am working with tables of loan associates, customers, companies and States versus numbers and amounts in my report and I want to create a main report with a subreport within it which shows the number of customers and their respective state that a loan associate has either written a new loan or serviced that customer and a total for each loan associate? Is there a report template out there that could address this for a complete report?
I have a main report with 2 subreports. My main report has a header section, the 2 subreports are in the detail section of my main report. How can i prevent my report from splitting up my header and my subreports. (Header section of my main report is a company and the subreports are the detail of the company.. i don't want my compnay name on the bottom of a page and then the detail on the next page.. it does this sometimes. thanks!
So I created a master report (in design time) and added subreports using VBA and now I get the components in the correct sequence, but how do I get the data to show up correctly because at run time the same query runs for each subreport component and I end up with the above sequence.
Defining the subreport query at design time won't work because then the subreport for a particular component will show the same data.
I need each subreport to recognize which data to show at run time - but I am not sure how to accomplish this.
I'm trying to stay away from duplicating subreports in the case where the same components are selected.
I've narrowed my problem to a subreport that prints multiple copies of the report based on the number of items generated in the subreport.
i.e. If I have an instructor registered to teach three classes in the subreport, the main report will print three time (identical). An instructor with two classes will print twice (identical).
No button...just good old "print" how? why? where?
In my database, I have a report called "rptEventsbyEmployee" and in that a subreport called "sbrptEventsbyEmployee". The master/child link is the employee name. Everything is fine until you get to page 3 where the subreport shows twice.
I have a report with several subreports that occassionally grow. I also have several fields that don't grow. If my subreport grows, it moves any other fields down as well. These are fields that are below the subreport, but not beneath it (e.g. on a lower line, but to the left).
Is there any way to keep the other fields stationary?
I have three reports that I have used as the subreports for one master report. The master report shows the three subreports on a page.
When one or more of the subreports contains no data the master report still shows the empty subreport. I would like subreports with no data to be left off the master report if this is possible.
For each of the individual subreports I have used the On No Data event to display a message and then not open the report if there is no data. This works fine for the individual subreports but when I open the master report the "empty" subreport is still displayed.
Is there a way to leave the subreport off the master report when there is no data in the subreport?
what i have is a form (image attached) and i need to be able to select any combination of parameters (including state, zipcode from GENERAL, i.e. referring doctor last name from RefMD, Drugname from Drugs, Diagnosisname from Diagnosis, and VisitType from Encounters) and filter PatientForm where all the selected parameters are true..
I am working on a report that will have 16 sub-reports, one right below the other. Because of the complex nature of the calculations in the groupings it seemed easier to "build" the report using vba rather than using the report's built-in grouping ability. The issue I am running into is that there seems to be no simple way to control the height of the sub-reports. I had hoped that setting "can grow" to yes would change the height of the sub reports and move all subsequent sub reports down (Allas)
As an alternative I thought too that I might be able to set the height of the sub-report control, which would make for a tricky but not impossible bit of programming, but (Again allas) I can't seem to find a reasonable way to determine the required height of the sub-report's control.
I am trying to create a P&L statement in access. I know what I want to come out at the end. I am just starting to play with access and having trouble getting what I want to come out of it.
On the sales side I have a query that gathers all the revenue sources and calculates a total for each date. I then use a second query to just take out the data I want for the P&L report. I created a sub-report that displays the data I want. I use the grouping and grand total features to get the total into the report footer. So far so good.
On the expense side I created a form of a query to manage the one to many relationship to capture the data for expenses (one purchase with many line items). I created a query based of this query to get the relevant data for my expense sub-report. I created the sub-report and got everything looking and calculating the way I want it to. I use the same grouping and grand total features to display the data in the report footer. Still good.
I created a new main parent report with the two sub-reports (sales & expenses) on it and even was able to pull the totals from the sub-reports into the main (so currently the subtotals of the two sub-reports are displayed twice). Now when I try to use the textboxes I used to pull the sub-report totals into the main report to perform additional calculations (sales - expenses) I get #error. I have tried different things and gotten ?name.
Control source for the two textbox controls on the main that display correctly, but don't let me do any further calculations.
=[rptP&LExpensesOverview]![AccessTotalsAmount]
=[rptP&LSalesOverview]![AccessTotalsTotal Sales]
To do the subtraction I have tried using the references above, as well as just using the names of the unbound text boxes in the report that bring the totals into the main report.
As a work around, I tried to build one query with all the data from sales and expenses, but can't "filter" based on date and get the data I want in the query results because the two sets of data are not necessarily related. I either get a long list of records, or no records (I am currently only playing with about 5 days of data).
I have a report that displays incidents, their details, consequences and a photo. Among the details is a severity rating high medium or low, I have been asked to make the report shorten the records which have been given a low severity (because it takes up as much space as the more important/severe ones).
The only method I can think of is to use the onformat event, to shrink and make invisible all the fields that I don't want to see if the severity field shows 'low'.
I have an existing Main form that has a sub form that the user uses to enter multiple records into a table....it works fine EXCEPT that I need to make it even easier and more intuitive and add a lot of labels. Basically the user selects items from a drop down list that adds items to a Work Order. I need to add some labels to the form to make it more descriptive for the user.
So, what I want to add multiple records using a single main form.
Is is possible to?:
1. simply turn the subform into a single main form? Can this be done by using a Command button or something similar?
2. copy all of the controls etc from the sub form into a new main form and have it all work nicely?
I have seven (sub) reports put on to a large report. How can I get the reports to shrink or grow according to how much info they have in them. I have tried Detail - Can Shrink, Can grow - but this doesn't seem to work. At the moment I have them all lined up but they are all overlapping each other.
I have a main report with one subreport. The main report contains details of a payment being made to a supplier. There is the total amount being paid, and the cheque number being transferred. There is also a field called payref which is made invisible. This is used to link up with the subreport. The amount is the total of several invoices for which payment is being made.
The subreport contains details of each of a number of the invoices being settled. The subreport is linked to the main report via a common field called pay-ref, and the subreport is 'visible'. The subreport prints when placed in the page header section of the main report. However when there is a large number of invoices being shown in the subreport only a certain number appear. It appears that the subreport can only hold a certain number.
When the subreport is put, as it should, in the detail section of the main report, it does not appear at all in the main report. I have no clue why and I have made several reports like this one before.
I am trying to get my totals from my subreports, and dividing the number by 2 or 4, and put the new number on the main report.
for Operator/Trainer Productivity, I used: =([rptEmployEvaluationOperator subreport].[Report]![OPTotal]+[rptEmployEvaluationOperatorTrainer subreport].[Report]![OPTotal])/2
for Auditor/Trainer Productivity, I used: =([rptEmployEvaluationAuditor1 subreport].[Report]![OPTotal]+[EmployEvaluationAuditor2 subreport].[Report]![OPTotal]+[EmployEvaluationAuditor3 subreport].[Report]![OPTotal]+[EmployEvaluationAuditorTrainer subreport].[Report]![OPTotal])/4
These both work if there are values in all totals subreports. When one of them might not have a total, I get an error message.
I have fields on the main menu where folks put in a start and end date prior to running a report. I'm doing a monthly report that pulls current month, and two prior months of data. For example, the user selects a date range of 10/1/14 to 10/31/14 the report will show August, September and October results.
I need the column headings to change each month depending on the date range they select to run the report so in November they will pull 11/1/14 to 11/30/14 and get September, October and November.
The headings for the columns would then adjust.
I put an unbound text box with the formula as follows in the control source of the report page header to pull the month that is equal to the current month minus two (I'm just showing one of the column headings formula):
I'm trying to create a report that contains data from a "main" form and a subform. However, I can't get the main form data to populate no matter what I try. I've been through all sorts of queries and just can't get it to work. The main form and subform are both separate tables, and there are no redundant fields.
Basically, the main form is an inventory of assets, and the subform is designed as a way to submit trouble tickets when one of those assets requires maintenance or repair.
I'm working on a database that produces employee contracts. To make it look as professional as possible I hide the Access main window when the splash screen loads using the ShowWindow function. The various forms to check and enter details all pop up without any problem, but when I get to the last stage where I use a report to produce the contract and then open it in PrintPreview mode, it won't display (unless I show the main window again , which looks very untidy!). Is it actually possible to display a report with the main Access window hidden?
I have a production report with 4 one to many sub reports (material reworked, reclassified, rejected, and issued). Each of those sub reports has a sum field totaling up the material reworked, rejected, etc for that lot.
I need a calculated field that subtracts the totals in each sub report from the reported amount produced in the main report.
Should I do this with a calculated field on the report. If so how would I reference the summed fields in the sub reports?
Or should I put a summed field of the amount reworked, rejected, etc in the query the report is based on and make a calculated field off that.?
I am trying to create a two-page report. I used a document entitled "Sophisticated Reports: Multiple Columns and Subreports" by Rich Gorvin to get where I am now. I created two (2) separate tabular subreports representing each page then placed them into a master report using a common group header as suggested in the paper. While the two (2) separate subreports print out in the desired tabular format, they do not do so in the master report document. The first page prints out just fine but the second page prints out each line of the table separately.
I am attaching a word document of the form I'm trying to re-create in Access.
I have created a report that includes 4 subreports. The subreports are displaying survey results from 4 different survey channels. New survey data is added to the tables monthly so the subreports will grow in size. Is there a way to have the subreports automatically resize and push down the subreport below? Currently what is happening is a subreport will grow and the additional data disappears underneath the next subreport. Is there a better way to create a report that displays information from multiple reports that will change in size?
