Have an issue that I have not been

[dougcranston]

Have an issue that I have not been able to find a solution for, although logically it should exist.

Posted this to another forum recently, and had no replies..And after reviewing Tek-Tips.Com I found a posting that I am not sure is similar/exactly the same issue that "izzyq" [New Page For Every Report] dated 8/9/02..

I am sure that I have read that there is a limit on the number of subreports, however, Starpassing in responding to "izzyq" [New Page For Every Report] posting has raised questions...

Background:
-----------
DB is an Access 97.mdb
What exists and is working: Have a departmental directory application that I have hosted on my web site. It is hierarchial in nature. On the web site, users select from various Headquarters Staff groups, Regional Staff or Stores located in a Region. Each is a separate page, and all is right with the world.

Problem Area:
-------------
Where I am having a problem is, upper management wants a directory laid out and published,
much like a phone book.

What was Requested:
-------------------
Ideally, they want:

Cover page
HQ Staff Group 1
HQ Staff Groug 2
.
HQ Staff Group n
Region Staff 1
Stores in Region 1
.
.
Region Staff n
Stores in Region n

[NOTE:
1) HQ Staff and Region Staff reports design/layout contain the same information elements. Stores contain more/additional information so it is substantially different from the HQ and Region Staff layouts.
2. HQ Staff, Region Staff and Store report layouts can and frequently DO span multiple pages.]

Page numbering consecutive 1-n

My Failure:
----------
I have created individual queries and reports, but for the life of me I have not found a way to generate the report as ONE CONTINOUS REPORT. As individual reports yes, but not as one "big" one.

Researched:
-------------
Have looked at Ken Getz's books, along with about 6 others that I own. Looked at ones on the shelf at Borders, and nothing has jumped out and smacked me as "HERE IS AN IDEA/SOLUTION" I can use to address this.

Have searched the web, and to be sure, I can/may be couching my queries off enough to not pull
back an answer.

Suggestions, or ideas where I might find a hint on how to deal with this?

Thanks for taking the time to read this post.. Hope some one has a workable suggestion.

Doug Cranston

 

[StarPassing]

I went back and checked on a large inspection checklist program I wrote in A97. I have one report that has a subreport in the heading - Cover Letter, and 19 subreports
that are large checklists. Most of the 19 subreports have more than 1 page. I resized each subreport to a height of about 1/4 inch and width of the page on the report. I put a pagebreak between each subreport. Page numbers are consecutive.
I know there are limits to the number of controls on a report, but it is a large number. Have not hit it yet.
Hope this helps.

 

[dougcranston]

Starpassing..

Thanks. I am trying this out now.. Have arbitrarily dropped 7 of the reports into a "main" of sorts... Is not pretty, but your suggestion appears to be in the direction that I want to go in.

Just have to dust off some A97 books and start reading up on Main vs Subreport, and build the final product... You have given me a hint on the master page, but what I built I have lost my column headers...

Bottom line I am further along now than I have been in three days, so thanks alot.

Thanks,

Doug

 

[MichaelRed]

I would approach this somewhat differently. As long as there is some commonallity between all of the sections, you should be able to create a single recorset with all info from all parts (HQ and stores). I aussume the info is all available to the same db -in some nmanner- so the superset (all records can be assembled). Although there may be a large number of blank fields in any given record, each record would have sufficient info to identify it's sort group. If this is true, the various header footer group options and sub sorts should cover the situation.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[dougcranston]

Michael,

I believe I followed your suggestion, however, I do not believe it will function in this situation.

The dB has several tables, but the key ones involved in this report are essentially:

tblHQ - contains records that have HQ and remote HQ staff directory information (All HQ staff)

tblRegionals - contains records that have Regional Staff directory information (all Regions)

tblStores - contains records that have Store specific directory information (all stores for all Regions)

Fields in tblHQ and tblRegionals are exactly the same. (Why the contractor did this, don't know..)

Fields in tblStores contains many add'l fields (what types of products sold, hours of operation, days open, etc.)

This layout was built by a contractor a couple of years ago, for web display. The need for this "phone book" is an outgrowth of mgt, not wanting to use hardcopies generated via the browser. This is one of those, "thou shalt's" rather than, why are we spending time doing this? type situations.

Thanks for the response..

Have a great weekend.

Dougc