How do I create Access report that shows columns of months?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to report on 30 +/- rows of information by month, with months
being in columns. I run into the 255 field limitation when I try to create an
array of fields that will do the job for a smaller number of rows.
 
J Manning said:
I am trying to report on 30 +/- rows of information by month, with months
being in columns. I run into the 255 field limitation when I try to create
an
array of fields that will do the job for a smaller number of rows.
Click to expand...

We're really going to need more information on what you have and what you
want -- otherwise, we'd just have to guess at the details. Don't forget...
we don't have your database in front of us to display the tables, etc..

What do you mean, for example, by "30+ rows of information, by month". Are
these records each summarizing one month, or ???

And, I am not sure what you did that resulted in your exceeding the 255
fields limit. In what context did you exceed it? What, exactly, did you do
to cause it to be exceeded?

It's really difficult enough to debug remotely when we have all the
information. It's just doggone near impossible without it.

Larry Linson
Microsoft Access MVP
 
Thank you, Mr Linson. I didn't want to be overly descriptive, hoping that my
problem might be "stock" enough to trigger a fairly standard answer.

I have a database with 500,000 records of insurance policies that are or
were in force over the past four years. The policies generally run for a year
and most of them renew for another year when they expire. In addition, new
policies are written on a steady basis. The policies are written through a
number of offices and each office is assigned to a unique region. I have
created tables that link expiring policies to renewal policies and I can tell
when a policy is new (the first one in a chain) and when a policy does not
renew (no succeeding policy in the chain). I have created a query that can
aggregate information by month, region, office, showing the premium values
and count for expiring policies, renewed policies, renewal policies, new
policies, and total of new and renewal policies. I created a report that
showed, one page per office per month, the values for the various indicated
categories plus some calculated information based on those categories to show
valuable relationships between the categories.

I now would like to take the report to another level so that the information
is presented by month and year-to-date (in columns) with the rows of the
report being the values for expired, renewed, renewal (by the way, the
"renewed" value is based upon the premium of an expired policy that was
renewed; and the "renewal" value is based on the premium of the policy that
succeeded that policy), new, and so on. I want to add some other elements
related to each of the offices and the month. When this is complete, there
will be about 30 rows.

I grouped by region by office and thought that I might be able to establish
a group for each "row", but there is a limit on the number of groups. I tried
to set up a calculated text box in the Office footer for each row and column
I needed and that is when I exhausted the 255 report-object limit. I set up
18 rows and 14 columns (a descriptor column, 12 months, and a year-to-date
column). There were also some report labels, page#, etc., that drove the
total number of objects over 255.

I hope this will provide enough of a sense of what I am doing but will
expand, if necessary. Thanks....John
 
I'm almost sorry I asked.

This is going to take some careful reading in the daytime. I'm really at a
loss to understand what "255 object limit" you ran up against. If my
recollection is correct, the limit on forms and reports is 700+ "things"
over the lifetime, but there is a way to reset it so you start over with
only the objects on the report now. (One way is the undocumented SaveAsText,
LoadFromText approach, but a simple import into a new database _may_ do what
you want.

On a quick read, you don't appear to have 255 objects (Controls, shapes,
etc.) on the report... each detail line is identical in structure, so those
objects/controls only count once each, not once for every line of data.

I'll look at it again tomorrow.

Larry Linson
Microsoft Access MVP
 
Hope your day is going well. You have managed to answer my question...on
another post!! I put chunks of the array in subreports and then mated the
subreports in the Office footer, the Region footer and the Report footer.
Works like a charm. There is probably a more direct way of doing this
but...what the heck...it works for me!

Thanks.... John
 
J Manning said:
Hope your day is going well. You have managed
to answer my question...on another post!!
Click to expand...

I'm glad I helped, even though I didn't realize I was doing so.

Chalk up another success for "Subreports Are Your Friend"!

Larry Linson
Microsoft Access MVP
 
Back
Top