asked on
Subreports in Access
Hello. I was wondering if someone might help me understand subreports in access a little better. I have a main report that gives a list of jobs filtered by a parameter that does a WHERE [JobNumber] LIKE Parameter query and returns the list of records.
This main report has a subreport that aggregates the values into a total subreport. It works without issue for the most part but a few things are odd to me. For example, if the main report has no results (the parameter was blank or the job number wasn't found) then the subreport will still show records from the database for some reason. Also, if I use a parameter like "1234*" then the main report will return all records like 1234A or 1234B etc. However, the subreport will only return records from the first JobNumber and not both.
The master and child fields are linked on JobNumber. I guess I was under the impression that the subreport would only show records from the main report but there seems to be something else that it is doing. Why does it show records when the main report doesn't? Why doesn't it show all of the records of the main report?
Thank you for your help.
This main report has a subreport that aggregates the values into a total subreport. It works without issue for the most part but a few things are odd to me. For example, if the main report has no results (the parameter was blank or the job number wasn't found) then the subreport will still show records from the database for some reason. Also, if I use a parameter like "1234*" then the main report will return all records like 1234A or 1234B etc. However, the subreport will only return records from the first JobNumber and not both.
The master and child fields are linked on JobNumber. I guess I was under the impression that the subreport would only show records from the main report but there seems to be something else that it is doing. Why does it show records when the main report doesn't? Why doesn't it show all of the records of the main report?
Thank you for your help.
* msaccess reportsMicrosoft Access* Access 2019* Access
Last Comment
prophet001
Depending on what you are aggregating, you may not even need the subreport.
If you create your main report with all of the fields you need to aggregate, then you can setup a group on the JobNumber, and instead of entering textboxes in the Detail section of the report, you can hide that section and add textboxes in the JobNumber group footer and use syntax like the following in the ControlSource of the textboxes:
=Sum([Amount])
or
=Count([JobNumber])
But in my experience, Jim is one of the best when it comes to Access reporting.
Dale
If you create your main report with all of the fields you need to aggregate, then you can setup a group on the JobNumber, and instead of entering textboxes in the Detail section of the report, you can hide that section and add textboxes in the JobNumber group footer and use syntax like the following in the ControlSource of the textboxes:
=Sum([Amount])
or
=Count([JobNumber])
But in my experience, Jim is one of the best when it comes to Access reporting.
Dale
ASKER
I checked the things you asked Jim and it seems that the relationship is OK. The subreport is in the footer section of the main report. It has been there since we made it and has worked OK for results which include one job number. If the search parameter for the JobNumber returns one job number then everything is fine. If the search parameter returns zero records or records from more than one job number then it seems to go weird. Here is a closer description of the setup:
Again, if the results are a single job number then the report and subreport populate without issue. I'm more concerned with understanding this and why it's happening or how subreports behave based on their linked fields.
However, I was able to get this to behave as I was hoping by adding a hidden text box to the main report which held the input parameters. I then pulled that text box value into the WHERE portion of the subreport's query and unbound the JobNumber fields from the main and subreports. This allows me to include more than one job number with a * search.
Thank you!
Main report recordset using a parameter for input 1234*
SELECT * FROM table WHERE JobNumber LIKE "1234*"
Main Report
-----------------------------------
ID Employee JobNumber Hours
-----------------------------------
1 Bob 1234A 8
2 Bob 1234B 4
3 Dan 1234B 7
Linked fields:
Master: JobNumber | Child: JobNumber
Subreport (Aggregate of hours by employee)
Even though the main report has 1234A and 1234B
the subreport will only include one of those in its
total. It won't include both and doesn't ask for
which one to include
-----------------------------------
Employee Hours (Aggregated on A)
-----------------------------------
Bob 8
OR
-----------------------------------
Employee Hours (Aggregated on B)
-----------------------------------
Bob 4
Dan 7
There is also the odd situation where Main Report has no records
but the subreport shows jobs that have an empty string JobNumber
over all the records in the database
Main report recordset using a parameter for input SOME-BAD-NUMBER
SELECT * FROM table WHERE JobNumber LIKE "SOME-BAD-NUMBER"
Main Report
-----------------------------------
ID Employee JobNumber Hours
-----------------------------------
No reults
Subreport
-----------------------------------
Employee Hours (Still shows records)
-----------------------------------
Bob 16
Dan 52
Mark 100
Joe 12
Again, if the results are a single job number then the report and subreport populate without issue. I'm more concerned with understanding this and why it's happening or how subreports behave based on their linked fields.
However, I was able to get this to behave as I was hoping by adding a hidden text box to the main report which held the input parameters. I then pulled that text box value into the WHERE portion of the subreport's query and unbound the JobNumber fields from the main and subreports. This allows me to include more than one job number with a * search.
Thank you!
<<The subreport is in the footer section of the main report. >>
You didn't say what type of footer, but that's the problem. If a sub report is not in the detail section, Access is not going to provide the correct key values for the master link.
One thing that is not obvious is that the master link can be a field or a control. So you can force a key value into the control, at which point sub report will report correctly for a job.
<<However, I was able to get this to behave as I was hoping by adding a hidden text box to the main report which held the input parameters. I then pulled that text box value into the WHERE portion of the sub report's query and unbound the JobNumber fields from the main and sub reports. This allows me to include more than one job number with a * search. >>
So your sub-report is not for a single job, but every job shown on the report? If so, then you did it correctly, and there should be no master/child links.
Master/Child links are only used when you want a sub report related to the current record that the report is working with.
Jim.
You didn't say what type of footer, but that's the problem. If a sub report is not in the detail section, Access is not going to provide the correct key values for the master link.
One thing that is not obvious is that the master link can be a field or a control. So you can force a key value into the control, at which point sub report will report correctly for a job.
<<However, I was able to get this to behave as I was hoping by adding a hidden text box to the main report which held the input parameters. I then pulled that text box value into the WHERE portion of the sub report's query and unbound the JobNumber fields from the main and sub reports. This allows me to include more than one job number with a * search. >>
So your sub-report is not for a single job, but every job shown on the report? If so, then you did it correctly, and there should be no master/child links.
Master/Child links are only used when you want a sub report related to the current record that the report is working with.
Jim.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Yes, that is correct. The sub report would should records related to the main report record based on the master/child linking.
<<Why does it show records when the main report doesn't? >>
A few things to check:
a. Make sure you have the sub-report in the detail section of the main report. If you don't, the sub report may be for a different job number.
b. Make sure the master and child and link fields are correct. The master should uniquely identify the main record across the entire report.
To check this, open the Recordsource for the main report. Do you see one record for each job? If not, there's part (or all) of your problem.
Jim.