Access 97 - Crosstab column limits

[Kitey]

I have created some crosstabs based on date criteria,
each relates to our financial year 01/04/2003 to
31/03/2004 for example. As there are up to 365(6) days
per year I am getting the error "Too Many Crosstab column
headers" is the limit set at 256? and can I get round it?

 

[Tom Ellison]

Dear Kitey:

I have some definite ideas on this one. So here comes.

If you're going to print a report of the crosstab, you're obviously
not going to get that across a single piece of paper unless the people
are going to read it with a magnifying glass. Your font size would
be, like, 1 point!

So, I'm thinking you want to put this on the screen and scroll it.

You're not going to be able to have more than 256 columns in the
datasheet. But there's another problem (to my way of thinking).
There is probably 1 or more columns on the left side that you want to
retain as you scroll the rest of the columns.

So, the solution to this is a form. Perhaps you show a month at a
time (with very, very narrow columns) or perhaps only a week or 10
days at a time.

I would put a horizontal scroll bar control on the form. The click
event of this control would act like it is scrolling, but actually it
would change the query in the form's RecordSource so it would return a
different 10 (or 7, or whatever) dates according to how it is
scrolled. This technique produces exactly what I would expect for the
behavior of such a setup. You can then have thousands of columns of
data if you wish.

There's a bit more to this, but I suggest you try to digest just this
much for now. Get back about any details of doing this that concern
you.

Warning: This will be a fairly expert level project.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[david epsom dot com dot au]

per year I am getting the error "Too Many Crosstab column

headers" is the limit set at 256? and can I get round it?

You can do 6 months at a time

FWIW, our reports show 15 columns. The users can select
days, weeks, months, years, and any start date.

We also export the same data to Excel in a column format,
out to however many rows it takes. If the user wants to
convert the rows to columns, Excel can do that just as well
as Access does.

(david)