Holy Grail of Access

[JEA]

I'm trying to do what seems to be the holy grail of MS Access:

A crosstab report with variable number of columns.

I've done quite a bit of research in to this. All the (well, 2) solutions
I've found require you to know the maximum number of columns there can ever
be. In my db I don't know that.

My crosstab has eployee ID's as column headings, dates as the row heading and
the value works out as the number of hours that staff member worked on that
day. The number of employees can change, and the user won't know how to
create a new report when it does, so I need the report to be able to cope
with a varying number of column headings.

Short of making the max. number of columns stupidly huge (very un-elegant and
not robust), does anyone know of a work around? Could I export the data to a
pre-formatted Excel template?

 

[Allen Browne]

You cannot add controls to the report (unless you switch to design view), so
you will need to know the max number of columns ahead of time, and provide
controls for them.

You do know the max number of columns that can ever be.
It is 255. Access will not generate any more columns than that.
Plan for up to 255 columns.

Alternatively, you can use a lower number if you build the PIVOT clause of
the crosstab query programmatically in Report_Open. Once you have the
predetermined max used up, you don't add any more to the PIVOT clause, and
you MsgBox the user that you can't handle all the columns.

 

[JEA]

My programming background's in java and C++. I'm comfortable with VBA as it's
a similar syntax. SQL, however, scares me. I've got most of my db done using
a mixture of VBA and wizards. I was hoping not to have to learn a completely
alien language, but it looks like I'll have to bite the bullet...

Thanks for the prompt reply, I'll give it a go.

 

[Allen Browne]

SQL is not that difficult.

You can mock up a query that does basically what you want, and switch to SQL
View to see a sample of what you need to generate.

Here's the low-down on creating the SQL statement for the crosstab query:
Crosstab query techniques: Specify column headings
at:
http://allenbrowne.com/ser-67.html#ColHead

And here's a way to get the SQL string from the query into VBA:
Copy SQL statement from query to VBA
at:
http://allenbrowne.com/ser-71.html

 

[JEA]

Thank you very much.

Looking at the SQL view of queries is what scared me! What would have taken
huge ammounts of VB can be done with a single statement! The statements look
simple enough, but as it's obviously such a powerful language, the syntax
looks complicated.

Thanks again.

SQL is not that difficult.

You can mock up a query that does basically what you want, and switch to SQL
View to see a sample of what you need to generate.

Here's the low-down on creating the SQL statement for the crosstab query:
Crosstab query techniques: Specify column headings
at:
http://allenbrowne.com/ser-67.html#ColHead

And here's a way to get the SQL string from the query into VBA:
Copy SQL statement from query to VBA
at:
http://allenbrowne.com/ser-71.html

My programming background's in java and C++. I'm comfortable with VBA as
it's

[quoted text clipped - 5 lines]

Thanks for the prompt reply, I'll give it a go.