Access Reports with SubReports

[glenfernandez]

HI there,

Need a little help with the reporting component of my MS Access 2002 project and would appreciate any insight / help from the experts. Please bear with me as I am still learning Access and this is my first project using it.

I'm generating a financial reporting system for a bank and have created a form for user input to dynamically generate the financial reports. So far everything is coming along ok. The reporting options allow the user to generate reports by:

1. Branch
2. Department
3. Group

Where several branches come under one dept and several depts come under one group.

Based on the above, I have three report templates: one for branch, one for dept and one for group that have their recordsource set to various saved queries (these saved queries have their parameters dynamically passed from the form). Now, I have been asked to do the following:

Print the main dept report AND include the individual reports for the branches belonging to that department as one file (report).

I know the key to it is using subreports. Whenever I have used subreports so far, It has meant including the sub-report at build time with the correct record source set for each subreport. This I can manage.

But in the current situation, I would have to include each subreport at 'run time'.
And this is where I am stuck:

When the user selects the dept, a query needs to select the correct branches to include as subreports. These subreports need to be created and included at 'runtime'. SO, there would never be a fixed number of subreports and the record source for each subreport would be generated dynamically.

If anyone can help me along I would really appreciate your ideas. Thank you for reading this and for any help/suggestions you may have for me.

Many Thanks!

Glen

[PianoMan64]

Hey Glen,

From the sounds of what you're talking about, you've gotten yourself into on of those situations where you're not sure how to go about getting the information outputted in the format that you want because of the design of the current system that you have.

Well, I have some good new for you. You're really close, you just need to understand a few functions that can be done within the reports themselfs that isn't that hard to understand since you've gotten as far as you have.

Keep in mind that in a report, you always have the option of grouping information from a source in any way that you want.

So the logical groups so far that you've expressed are Branches, Departments, groups.

So the break-down is this You can have multiple groups within one department. You can have multiple departments within one branch and of course you can have multiple branches.

So this is how you're going to have to break it down.

1. First off, you don't need to create a subreport from what you've told me so far. so as far as that goes, that's out for now.

2. If you simply create a grouping by Branch and then by department, and you have those create a new page when those change, that would take care of being able to print multiple reports based on the department they select.

3. If you're wanting to include all your branches as part the report, then the design of the structure needs to change so that Departments are at the top, then branches within those departments and so on.

I do have one question though, Are you going to want to select simply a department only, or sometime are you going to want to print departments by selected branches?

Let me know on that and I can give you more details as to how to set that up.

Hope that helps,

Joe P.

[glenfernandez]

HI Joe!

Thanks for the quick reply.

To answer your question, it would be set up such that if I choose to run a report for a department then the first report that shows would be the department totals. This should then be followed by multiple reports for each branch within that department. This means I would not have to cater for "printing departments by selected branches". I hope I have answered your question on that point.

Now, lets see if I understand what you were trying to point at. If I understood you correctly, I should create one report template and populate the template with a saved query based on data at the Group (topmost) level. Then using sorting and grouping I break this down into department and branch so I can expose what is needed. If that is correct, then I think I understand the concept of what you are suggesting.

However, I may have trouble implementing it. This is my current scenario. Each report right now consists of a main report and a subreport - because the main and subreport reference the same column headers but utilise different formulas. Within each report, I have already used sorting and grouping at four levels to present the data in the correct format.

Using a template like this, I have three versions - one for Group, one for Department and one for Branch. Each of these versions is driven by a saved query (btw Joe, you have already looked at my queries the last time - see post "Speeding Up A Query" around Nov 14th. You helped me speed them up. Those were snippets of the queries that drive these reports).

So - if I had understood correctly what you were suggesting above, im still not sure how to make it work given my current reporting structure. I already find my current reporting structure very cumbersome - it was the only way i knew how to do it - and am worried because my project deadline is looming and I am really stuck at this point. Any pointers and tips on how to proceed would be much appreciated.

Once again, thanks very much for your reply and kind help Joe! :)

cheers...

glen

[PianoMan64]

hey Glen,

yes, you understood what I was trying to say.

The problem that we have is at this point, is I need to be able to see the query and how you have that displaying, and where you have issued your break points or grouping within your report.

if you can include a copy of your query along with some sample data, that would be great.

What I really think you should do is simplify the stucture since time is of the essence here. I would have multiple reports that run depending on what you want. then instead of trying to make one report does it all.

You can have multiple reports and selected either by input or by code, which report needs to run and which data source is going to be use. This way it makes it easier for the user to understand , it is simple for the programmer to follow.

So if you can include that information and some sample data, I can give it a try and see what I can come up with. I am flying a little blind here since I don't remember much of your table structure. All I remember is it was a great deal of linked tables.

Look forward to your response,

Joe P.

[glenfernandez]

Hi Joe,

I will be sending you a PM in a bit.

Thanks

Glen

[PianoMan64]

@glenfernandez
Hey Glen,

I've started to look over your code that you have behind the form.

The first question that I have is "What is the reason for the Linked tables?" From what I can tell, you don't reference any of the fields that are in any of the linked tables at all. Are you using it for some kind of sorting of data in some manner other than Ascending?

Below is the copy of the first Select Statment that you have in the "GOT Reporting" Form under the BtnRptGroup_Click(). I've reformated it so it will make it a great deal easier to read. Let me know. Then we can work through this.

Let me know?

strSQL = "SELECT tblTransactions.MIS_Org AS [MIS ORG], " & vbCrLf & _
         "       tblTransactions.GL_Code AS [GL CODE], " & vbCrLf & _
         "       tblTransactions.Month AS [Month], " & vbCrLf & _
         "       tblTransactions.Year AS [Year], " & vbCrLf & _
         "       Nz(Round(Sum(tblTransactions.Amount),2),0) AS Amount " & vbCrLf & _
         "FROM (((((tblTransactions LEFT JOIN tblMonth ON tblTransactions.Month=tblMonth.Month)" & vbCrLf & _
         "                          LEFT JOIN tblMIS_Org ON tblTransactions.MIS_Org=tblMIS_Org.MIS_Org)" & vbCrLf & _
         "                          LEFT JOIN tblDepartment ON tblMIS_Org.Department_ID=tblDepartment.ID)" & vbCrLf & _
         "                          LEFT JOIN tblGroup ON tblDepartment.Group_ID=tblGroup.ID)" & vbCrLf & _
         "                          LEFT JOIN tblGLLine ON tblTransactions.GL_Code = tblGLLine.GL_Code)" & vbCrLf & _
         "                          LEFT JOIN tblBudgetLine ON tblGLLine.[BUDGET LINE ACCT]=tblBudgetLine.[BUDGET LINE ACCT] " & vbCrLf & _
         "WHERE ((tblGroup.Group)" & strGroup & ") " & vbCrLf & _
         "  AND ((tblMIS_Org.Location) " & strLOC & ") " & vbCrLf & _
         "  AND ((tblMIS_Org.Reporting) " & strREP & ") " & vbCrLf & _
         "GROUP BY tblTransactions.MIS_Org, tblTransactions.Month, tblTransactions.GL_Code, tblTransactions.Year;"
 
 

Also from what I can tell from your code, you've been creating a great deal of queries, and are duplicated with slight changes between each one. I think you would be better off trying to simplify the structure and then assigning the query that you want to the recordsource of the report, assuming that the fields are the same for the report that you're attaching it too.

You would set the value of the report through code like this:

Docmd.OpenReport "NameOfReport", acDesignView
Reports![NameOfReport].Recordsource = "Your Select Statement Here"
Docmd.Close acReport, "NameOfReport", acSaveYes
 

Hope that helps some,

Joe P.

[glenfernandez]

HI Joe,

Thanks for looking through my code.

To explain my code, the first two strSQLs ("QryByMIS" and "QryByMISOP") are being used to restrict the number of records I have to work with. QryByMIS pulls the relevant data from the (Monthly) Transactions table and QryByMISOP from the OP (Budget table).

The linked tables are there because:

1) The JOINS on tblMIS_Org, tblDepartment, tblGroup and tblMonth allow for the "filtering" of the recordset mentioned above which keeps the number of records I have to work with at the minimum level for each subsequent query.

2) There may not be a transaction for every item every month. Some items may not even incur a transaction all year. Thus the LEFT JOIN on tblGLLine ensures that all transactions are shown in the Report, with NULL transactions shown as ZERO. WIthout joining on tblGLLine I would not be able to guarantee a full complement of GL (General Ledger) Items in each report.

3) The LEFT JOIN on tblBudgetLine allows me to sort and group the records according to the Expenditure sequence and grouping that the report requires. This is not by any logical or ascending order except what was determined by the original author of the report. This sequence has since become the standard format and cannot be changed.

Thats the first bit. The next set of 10 queries use the recordsets above to calculate and present the data on the ten distinct columns of the report. Because each follows its own specific formulae, I have had to keep them as separate saved queries. Finally the last two queries:

1) Query "GOT By MIS Org" adds another three columns that are calculated from the original ten queries above and pulls all thirteen columns together in the correct sequence. This then serves as the recordsource for the MAIN REPORT.

2) Query "QryHeadCountMIS" also generates thirteen columns that reference the same column headers BUT operate on different formula for each column. This then becomes the recordsource for the SUB-REPORT.

I do agree with you that my current procedure seems very inefficient, but being my first access exposure, I am struggling with making it more elegant. SInce my last post, I have tried reworking my structure so I dont have to repeat this whole process for each level - MIS (Branch), DEPARTMENT and GROUP. I believe I should be able to nail that one down by the weekend.

BUT, my problem - and the user has stressed that this is ESSENTIAL - is:
When the user selects a department, two things should happen:
1) The department report is printed
2) Individual reports for all the MIS (Branch) belonging to that Department are printed as well.

Ideally the above should generate these reports as one file. And here, I am well and truly buggered :(

I'm at my access limits and simply cant figure that bit out. I can live with unelegant code (given the time constraints) but if the reporting feature above is not delivered, it may mean the project will not fly. And that would not be good for me - haha.

Well, I hope I've explained why I've done things the way I have - its not a good reason but Im working with what I know. I will certainly try and incorporate your code re-write as I refine my code today.

Once again, my sincerest thanks for all your help.

Glen

[PianoMan64]

@glenfernandez
Hello again Glen,

After reading your long answer to my question, let see if we can break this down to something I can even begin to rap my head around.

1. I need to know where it is that you need the real help here, and what it is that you're wanting me to provide you help with.

It seems with all these conversations, I've lost the ball on this one. Can we nail down one peace that we're having problems with and that way we can start to go through this peace by peace, and make sure every step is covered.

the smaller the peace to work with, then we can work through it that much quicker. In the mean time, that will give you a chance to rest your head while I try to peace all this together.

Talk to you soon,

Joe P.

[PianoMan64]

Hey Glen,

I have an idea here. If all you're really wanting to know is how to set the recordsource for each report and combine then together based on the primary report, then my eariler suggestion should be able to do the trick. Since you know what query needs to be in each report to get the result you want, wouldn't it make sense, based on the answer I gave you, that would would simple save the RecordSource value for each report, and when you're complete, then run the main report with all the subreports in them?

Let me know if that helps, or not?

Thanks,

Joe P.

[glenfernandez]

Hey Joe,

Sorry for the long reply before.

This is my problem:

BUT, my problem - and the user has stressed that this is ESSENTIAL - is:
When the user selects a department, two things should happen:
1) The department report is printed
2) Individual reports for all the MIS (Branch) belonging to that Department are printed as well.

Yes, what you had suggested initially should do the trick BUT how do I set the query as a recordsource and how do I then use that recordsource in my report??

Also, how would I set it up such that selecting a Department would print the department totals report and individual reports for all the branches under that department?

Thanks for any help you can provide.

Glen

[glenfernandez]

HI Again

anybody with any help to point me in the right direction?

Many Thanks

glen