Sub level reports

[Very Basic User]

Hello,
I have a main report that I call "game plan" Within the report, I currently
have several sub-level reports that draw their information from a seperate
query. For example. On the main report for an area, Each catagory (Issue,
Repair, Safety, etc.) are included in a seperate sub-level report. Because
there are about 8 sub-level reports with 8 seperate queries driving the
information gathering, the report takes too long to open. I was wondering if
there is a way to have each sub level report draw from one query with any
adjustments in the properties?

 

[BruceM]

It is hard to say without knowing more about how your database is
orgainized. In particular you should describe briefly the database's
purpose, and show or describe the table structure.

One thing you may be able to do is group by category. Is there a separate
table for each subreport? How is the sub-level information related to the
main information?

 

[Very Basic User]

Thanks Bruce,

Let me try to describe. The database is to organize and prioritize a plants
follow ups and issues. The data base splits the plant into focus areas and
each focus area has a leader that is accountable for all the followup items
in the focus area. The follow up items are then devided by type like (Safety,
Issue, Repair, CI, etc) There are only two main tables. A table with the top
level information about a issue, and a sub level table that us used as an
action register. Each table tied together with an auto number for the issue.
In the main tables form, you can select the "Type" I.E. Safety. I then have a
main report for each focus area called game plan that will pull from the main
table the focus area and focus area leader. This report I call game plan. It
consists of a main report with about 8 sub level reports. Each sub level is
for the "type" and is prioritized using A, B, C. This way a bulk area leader
can look at all active issues and they are grouped on the main report in sub
reports for each type. Now for the sub level reports. I still used only the
main and sub table, but I created a simple report for each "Type" and tie it
to its own seperate query with a criteria statement for that "Type" So when I
open the "game plan", one query is used to select focus area, and 8 seperate
queries run to populate each 8 "Types" reports. This seemed to be the only
way I could organize the full focus area game plan on one form.

If I try to answer your question specifically.
1. Main table has (Auto Number, Focus area, Focus area leader, Priority,
Breif description, Entered - Completed Date, Type, Results)
2. Sub table is tied to main through auto number and contains (Action items,
action item owner, status, expected completion, and notes)

For the game plan report, I don't even pull from the sub level table as the
game plan only contains the brief description statement, #, and priority. If
a user wants to see action items they can run a seperate report. The only
report that takes too much time is the overall game plan that pulls from the
main table through a query and pulls each "Type" from the main table through
seperate queries.

I hope this helps! and thank you for your time
--
Thank you for your time!
John

It is hard to say without knowing more about how your database is
orgainized. In particular you should describe briefly the database's
purpose, and show or describe the table structure.

One thing you may be able to do is group by category. Is there a separate
table for each subreport? How is the sub-level information related to the
main information?

 

[BruceM]

Do I understand correctly that the main table is for recording issues
(problems, recommendations, etc.), and the related table for listing items
relevant to the issue?

If so, you should have something like this:

tblMain (Main table)
IssueID (autonumber primary key)
FocusArea
Type
etc.

tblActionItems
ActionItemID (autonumberPK)
IssueID (Number field, related to IssueID in tblMain)
AI_Description
AI_Owner
etc.

Regarding the slow report, as I understand it is based on a query that is
based on tblMain. Is that correct? If so, just group the report by Type.
To do this, use the Sorting and Grouping dialog (View >> Sorting and
Grouping). Select Type, and choose the option for a group header. After
that, select another field for ordering within each Type. Close the dialog.
In the report, add to the group header a text box bound to Type. Forget the
subreports, and get rid of the eight queries.

Thanks Bruce,

Let me try to describe. The database is to organize and prioritize a
plants
follow ups and issues. The data base splits the plant into focus areas and
each focus area has a leader that is accountable for all the followup
items
in the focus area. The follow up items are then devided by type like
(Safety,
Issue, Repair, CI, etc) There are only two main tables. A table with the
top
level information about a issue, and a sub level table that us used as an
action register. Each table tied together with an auto number for the
issue.
In the main tables form, you can select the "Type" I.E. Safety. I then
have a
main report for each focus area called game plan that will pull from the
main
table the focus area and focus area leader. This report I call game plan.
It
consists of a main report with about 8 sub level reports. Each sub level
is
for the "type" and is prioritized using A, B, C. This way a bulk area
leader
can look at all active issues and they are grouped on the main report in
sub
reports for each type. Now for the sub level reports. I still used only
the
main and sub table, but I created a simple report for each "Type" and tie
it
to its own seperate query with a criteria statement for that "Type" So
when I
open the "game plan", one query is used to select focus area, and 8
seperate
queries run to populate each 8 "Types" reports. This seemed to be the only
way I could organize the full focus area game plan on one form.

If I try to answer your question specifically.
1. Main table has (Auto Number, Focus area, Focus area leader, Priority,
Breif description, Entered - Completed Date, Type, Results)
2. Sub table is tied to main through auto number and contains (Action
items,
action item owner, status, expected completion, and notes)

For the game plan report, I don't even pull from the sub level table as
the
game plan only contains the brief description statement, #, and priority.
If
a user wants to see action items they can run a seperate report. The only
report that takes too much time is the overall game plan that pulls from
the
main table through a query and pulls each "Type" from the main table
through
seperate queries.

I hope this helps! and thank you for your time

 

[Very Basic User]

I tried this. It does work by grouping by type, but I can't get it to group
by type from left to right. It wants to put it all in a line up and down. I
want to try to get each type to go from left to right and each issue within
the type to go up and down starting at "A" priority. Is this possible?

 

[BruceM]

I'm afraid I don't have a ready reply to that question. I have some general
ideas, but cannot confidently recommed anything.

If the report is loading at an acceptable speed you are probably taking the
right general approach, but it will probably be best to repost in the
Reports group. Since there is only one table involved in the report, post
basic information about the table, explain that you have grouped by Type,
and sorted by (whatever exactly) within each type, and that it works OK but
you want to do a one-page report with Types across the top and (whatever
exactly) listed under each type.

Be sure to mention you posted in this group, but were urged to post again in
Reports. Also, respond to this post with a message stating you have started
a new thread in the Reprots group under the subject (Subject Line of new
message).

The web interface should be able to direct you to
microsoft.public.access.reports

 

[Very Basic User]

Hello Bruce, and thank you for the advice. I will continue to search on an
easier way. I did find one option that reduces the # of queries needed by
just using the sub-report "on filter" options...

You can set the Filter property of the report to:
[Idea Type] = 'safety'
Set the FilterOn property to Yes

This allowed me to leave the criteria in my query open but the report will
call for whatever I want filtered, Now I just need to figure out how to make
one report pull different information. That would take me from 8 queries and
8 reports to one of each.