By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,045 Members | 1,372 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,045 IT Pros & Developers. It's quick & easy.

Report Grouping

100+
P: 104
I have a table structure such that my jobs are broken down into components. My ComponentID is a foreign key in five different tables: bids, directives, specifications, transactions, and updates. I know how to create a master report and include subreports of each one of these tables separately, but this requires a separate query for each subreport. Is it possible to create a report that goes through each component individually and shows its bids, directives, specifications, transactions, and updates before moving on to the next component?
Mar 4 '14 #1

✓ answered by NeoPa

If that's where you're falling short then Referring to Items on a Sub-Form should help Gary.

Share this Question
Share on Google+
8 Replies


Expert 100+
P: 1,240
Yes, it is likely possible, but surely using sub-reports is by far the simplest way to accomplish what you are doing. Since each of those 5 tables can have multiple rows doing that any other way would be pretty tedious and full of pitfalls. If I'm managing your project I'd need a stronger reason than "too many queries" to spend time changing it.

Jim
Mar 4 '14 #2

100+
P: 104
Hi jimatqsi,
My main reason for wanting to group this the way I mentioned is to keep these components together. It is for a general contracting database I'm working on and by the end of the job I'm going to have about thirty different components. I want each component to step through all of the different sections. For example, the job specifications, bids, and updates will all be in grouped together instead of having to scroll through the whole report to find one component's details.

This is a problem I've had in the past with other applications and I think it is a valuable lesson. I'm pretty comfortable with VBA, so if you have a complicated solution I'd like to give it a shot.
Thanks
Mar 4 '14 #3

Rabbit
Expert Mod 10K+
P: 12,430
I don't understand what you mean by "keeps these components together" because subreports allow you to choose fields to link the master and child report. Please explain.
Mar 4 '14 #4

100+
P: 104
From my understanding, my five subreports for the tables I have listed above would each step through all of the job components separately. In other words, my subreport for bids would step through each component of the job, then the one for directives would step through each component of the job, and so on. What I'm looking to do is have the report grouped by job component, then each component steps through its corresponding bids, directives, specifications, transactions, and updates.

The issue that I am foreseeing is that the query for my main report wouldn't be able to group my results accordingly. I have no problem using multiple queries if that is the solution. I need to filter the main report by Job and show all of its components listed below it.

JobID is the FK in Job Components, and ComponentID is the FK in five different tables, all of which are combined into a single report.
Mar 4 '14 #5

Expert 100+
P: 1,240
You may not realize that you can have a sub-report on the sub-report. So the main report would be for the jobs. The detail in the jobs report would list individual components. Each component could link to a sub-report, or multiple sub-reports. If directives have specifications, or bids have transactions, each of those sub-reports could link to corresponding sub-sub-reports.

Your sub-reports don't have to be aligned vertically. I have lined up sub-reports within a report like columns. You could have a "column" showing your bids, another "column" showing the directives and so on.

The query for your main report only needs to worry about grouping the data by job and component. Each component will link to subreport(s) that manage their own organization of their data.

Jim
Mar 4 '14 #6

100+
P: 104
"You may not realize that you can have a sub-report on the sub-report. So the main report would be for the jobs. The detail in the jobs report would list individual components. Each component could link to a sub-report, or multiple sub-reports."

Hi Jim,
This is exactly what I'm trying to do. My question is how do I use the main report's grouping to filter my sub reports?

Directives don't have specifications, and bids don't have transactions. Job components (such as excavation or electrical) have bids, directives, specifications, transactions, and updates.

So my main report will group records by the job component, and then the sub-report in the detail section needs to be filtered by that component (in other words, the value of the control in the group header).

I know how to create query filters based on controls in a form or by using expressions, but I don't know how to refer to a report's controls.

I hope this is more clear.
Thanks,
Gary
Mar 4 '14 #7

NeoPa
Expert Mod 15k+
P: 31,769
If that's where you're falling short then Referring to Items on a Sub-Form should help Gary.
Mar 5 '14 #8

100+
P: 104
Thanks everyone, I got it to work. I was making it a lot more complicated than I should have been.
Mar 6 '14 #9

Post your reply

Sign in to post your reply or Sign up for a free account.