Crosstab Report

[Jean]

Hi,

I am using MS ACCESS XP. I setup an crosstab query and want to create a
crosstab report but following the Help file instruction and just realized,
the report columns are fixed and not dynamic. Is there any solution that
will generate the crosstab dynamic column?

I was thinking about dumping the query data directly to an Excel
spreadsheet, if MS ACCESS report don't support dynamic columns...

Thanks

Jean

 

[Allen Browne]

As you found, the report does not cope with column names that come and go as
they do in a crosstab query.

Some options:

Option 1. Specify the column names in the query's Column Headings property.
If the column names are fixed (though not necessarily always present), you
can type them into the Column Headings property of the query (Properties
box, in query design view.) For example, if the your column heading field
is:
Month([SaleDate])
but there may not be any records for month 12 until the end of the year, you
could enter all the numbers in Column Headings.

Option 2. Save the report with unbound controls, and assign in Report_Open.
You can save the report without enough text boxes to cope with the maximum
number of columns you will ever need. The text boxes are unbound, and the
report's RecordSource is blank.

Then in the Open event of the report, generate the crosstab query statement
dynamically. Assign this string to the report's RecordSource, and assign
each of the column heading fields to the Control Source of the appropriate
text box. Set the Left and Width of each box, and the Caption of the label
above the column, and hide the unused text boxes and labels.

This takes a bit off effort, but does produce very flexible reports.

3. Option 3. Build the report dynamically.
It is possible to CreateReport() and CreateControl(), setting it up for
whatever columns you determine you need at runtime.

I do not recommend this 3rd approach, as a) it is more complex; b) it does
nothing that option 2 cannot do; c) it stops you generating an MDE; d) it
bloats the database; e) it leaves the user with questions about saving the
report when they close it.

 

[Duane Hookom]

Option 4. Use the method in the Crosstab.mdb demo found at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. This solution is very
efficient, uses less code than Option 2, and is much more flexible.

--
Duane Hookom
MS Access MVP

As you found, the report does not cope with column names that come and go
as they do in a crosstab query.

Some options:

Option 1. Specify the column names in the query's Column Headings
property.
If the column names are fixed (though not necessarily always present), you
can type them into the Column Headings property of the query (Properties
box, in query design view.) For example, if the your column heading field
is:
Month([SaleDate])
but there may not be any records for month 12 until the end of the year,
you could enter all the numbers in Column Headings.

Option 2. Save the report with unbound controls, and assign in
Report_Open.
You can save the report without enough text boxes to cope with the maximum
number of columns you will ever need. The text boxes are unbound, and the
report's RecordSource is blank.

Then in the Open event of the report, generate the crosstab query
statement dynamically. Assign this string to the report's RecordSource,
and assign each of the column heading fields to the Control Source of the
appropriate text box. Set the Left and Width of each box, and the Caption
of the label above the column, and hide the unused text boxes and labels.

This takes a bit off effort, but does produce very flexible reports.

3. Option 3. Build the report dynamically.
It is possible to CreateReport() and CreateControl(), setting it up for
whatever columns you determine you need at runtime.

I do not recommend this 3rd approach, as a) it is more complex; b) it does
nothing that option 2 cannot do; c) it stops you generating an MDE; d) it
bloats the database; e) it leaves the user with questions about saving the
report when they close it.


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am using MS ACCESS XP. I setup an crosstab query and want to create a
crosstab report but following the Help file instruction and just
realized,
the report columns are fixed and not dynamic. Is there any solution that
will generate the crosstab dynamic column?

I was thinking about dumping the query data directly to an Excel
spreadsheet, if MS ACCESS report don't support dynamic columns...

Thanks

Jean

 

[Jean]

Great Help! Thanks to you both Allen and Duane.

Option 4. Use the method in the Crosstab.mdb demo found at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. This solution is very
efficient, uses less code than Option 2, and is much more flexible.

--
Duane Hookom
MS Access MVP

As you found, the report does not cope with column names that come and go
as they do in a crosstab query.

Some options:

Option 1. Specify the column names in the query's Column Headings
property.
If the column names are fixed (though not necessarily always present), you
can type them into the Column Headings property of the query (Properties
box, in query design view.) For example, if the your column heading field
is:
Month([SaleDate])
but there may not be any records for month 12 until the end of the year,
you could enter all the numbers in Column Headings.

Option 2. Save the report with unbound controls, and assign in
Report_Open.
You can save the report without enough text boxes to cope with the maximum
number of columns you will ever need. The text boxes are unbound, and the
report's RecordSource is blank.

Then in the Open event of the report, generate the crosstab query
statement dynamically. Assign this string to the report's RecordSource,
and assign each of the column heading fields to the Control Source of the
appropriate text box. Set the Left and Width of each box, and the Caption
of the label above the column, and hide the unused text boxes and labels.

This takes a bit off effort, but does produce very flexible reports.

3. Option 3. Build the report dynamically.
It is possible to CreateReport() and CreateControl(), setting it up for
whatever columns you determine you need at runtime.

I do not recommend this 3rd approach, as a) it is more complex; b) it does
nothing that option 2 cannot do; c) it stops you generating an MDE; d) it
bloats the database; e) it leaves the user with questions about saving the
report when they close it.


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am using MS ACCESS XP. I setup an crosstab query and want to create a
crosstab report but following the Help file instruction and just
realized,
the report columns are fixed and not dynamic. Is there any solution that
will generate the crosstab dynamic column?

I was thinking about dumping the query data directly to an Excel
spreadsheet, if MS ACCESS report don't support dynamic columns...

Thanks

Jean

 

[Jaybird]

Mr. Hookom,

You and Mr. Browne have been most helpful to me in my efforts to understand
this and other Access issues. This link intrigues me very much, but it is
apparently no longer valid. Has it moved to another location?

Jaybird

Option 4. Use the method in the Crosstab.mdb demo found at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. This solution is very
efficient, uses less code than Option 2, and is much more flexible.

--
Duane Hookom
MS Access MVP

As you found, the report does not cope with column names that come and go
as they do in a crosstab query.

Some options:

Option 1. Specify the column names in the query's Column Headings
property.
If the column names are fixed (though not necessarily always present), you
can type them into the Column Headings property of the query (Properties
box, in query design view.) For example, if the your column heading field
is:
Month([SaleDate])
but there may not be any records for month 12 until the end of the year,
you could enter all the numbers in Column Headings.

Option 2. Save the report with unbound controls, and assign in
Report_Open.
You can save the report without enough text boxes to cope with the maximum
number of columns you will ever need. The text boxes are unbound, and the
report's RecordSource is blank.

Then in the Open event of the report, generate the crosstab query
statement dynamically. Assign this string to the report's RecordSource,
and assign each of the column heading fields to the Control Source of the
appropriate text box. Set the Left and Width of each box, and the Caption
of the label above the column, and hide the unused text boxes and labels.

This takes a bit off effort, but does produce very flexible reports.

3. Option 3. Build the report dynamically.
It is possible to CreateReport() and CreateControl(), setting it up for
whatever columns you determine you need at runtime.

I do not recommend this 3rd approach, as a) it is more complex; b) it does
nothing that option 2 cannot do; c) it stops you generating an MDE; d) it
bloats the database; e) it leaves the user with questions about saving the
report when they close it.


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am using MS ACCESS XP. I setup an crosstab query and want to create a
crosstab report but following the Help file instruction and just
realized,
the report columns are fixed and not dynamic. Is there any solution that
will generate the crosstab dynamic column?

I was thinking about dumping the query data directly to an Excel
spreadsheet, if MS ACCESS report don't support dynamic columns...

Thanks

Jean

 

[Duane Hookom]

The site seems to have some issues once in a while. Generally if you wait a
couple hours, it is back up and running. I no longer work for the company
but did talk with one of the owners last night and he didn't mention
anything about their web presence.
--
Duane Hookom
MS Access MVP

Mr. Hookom,

You and Mr. Browne have been most helpful to me in my efforts to
understand
this and other Access issues. This link intrigues me very much, but it is
apparently no longer valid. Has it moved to another location?

Jaybird

Option 4. Use the method in the Crosstab.mdb demo found at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. This solution is
very
efficient, uses less code than Option 2, and is much more flexible.

--
Duane Hookom
MS Access MVP

As you found, the report does not cope with column names that come and
go
as they do in a crosstab query.

Some options:

Option 1. Specify the column names in the query's Column Headings
property.
If the column names are fixed (though not necessarily always present),
you
can type them into the Column Headings property of the query
(Properties
box, in query design view.) For example, if the your column heading
field
is:
Month([SaleDate])
but there may not be any records for month 12 until the end of the
year,
you could enter all the numbers in Column Headings.

Option 2. Save the report with unbound controls, and assign in
Report_Open.
You can save the report without enough text boxes to cope with the
maximum
number of columns you will ever need. The text boxes are unbound, and
the
report's RecordSource is blank.

Then in the Open event of the report, generate the crosstab query
statement dynamically. Assign this string to the report's RecordSource,
and assign each of the column heading fields to the Control Source of
the
appropriate text box. Set the Left and Width of each box, and the
Caption
of the label above the column, and hide the unused text boxes and
labels.

This takes a bit off effort, but does produce very flexible reports.

3. Option 3. Build the report dynamically.
It is possible to CreateReport() and CreateControl(), setting it up for
whatever columns you determine you need at runtime.

I do not recommend this 3rd approach, as a) it is more complex; b) it
does
nothing that option 2 cannot do; c) it stops you generating an MDE; d)
it
bloats the database; e) it leaves the user with questions about saving
the
report when they close it.


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.


I am using MS ACCESS XP. I setup an crosstab query and want to create
a
crosstab report but following the Help file instruction and just
realized,
the report columns are fixed and not dynamic. Is there any solution
that
will generate the crosstab dynamic column?

I was thinking about dumping the query data directly to an Excel
spreadsheet, if MS ACCESS report don't support dynamic columns...

Thanks

Jean

 

[Paulius]

this link doesn't work, can someone give an alternative link or something
like that?

The site seems to have some issues once in a while. Generally if you wait a
couple hours, it is back up and running. I no longer work for the company
but did talk with one of the owners last night and he didn't mention
anything about their web presence.
--
Duane Hookom
MS Access MVP

Mr. Hookom,

You and Mr. Browne have been most helpful to me in my efforts to
understand
this and other Access issues. This link intrigues me very much, but it is
apparently no longer valid. Has it moved to another location?

Jaybird

Option 4. Use the method in the Crosstab.mdb demo found at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. This solution is
very
efficient, uses less code than Option 2, and is much more flexible.

--
Duane Hookom
MS Access MVP


As you found, the report does not cope with column names that come and
go
as they do in a crosstab query.

Some options:

Option 1. Specify the column names in the query's Column Headings
property.
If the column names are fixed (though not necessarily always present),
you
can type them into the Column Headings property of the query
(Properties
box, in query design view.) For example, if the your column heading
field
is:
Month([SaleDate])
but there may not be any records for month 12 until the end of the
year,
you could enter all the numbers in Column Headings.

Option 2. Save the report with unbound controls, and assign in
Report_Open.
You can save the report without enough text boxes to cope with the
maximum
number of columns you will ever need. The text boxes are unbound, and
the
report's RecordSource is blank.

Then in the Open event of the report, generate the crosstab query
statement dynamically. Assign this string to the report's RecordSource,
and assign each of the column heading fields to the Control Source of
the
appropriate text box. Set the Left and Width of each box, and the
Caption
of the label above the column, and hide the unused text boxes and
labels.

This takes a bit off effort, but does produce very flexible reports.

3. Option 3. Build the report dynamically.
It is possible to CreateReport() and CreateControl(), setting it up for
whatever columns you determine you need at runtime.

I do not recommend this 3rd approach, as a) it is more complex; b) it
does
nothing that option 2 cannot do; c) it stops you generating an MDE; d)
it
bloats the database; e) it leaves the user with questions about saving
the
report when they close it.


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.


I am using MS ACCESS XP. I setup an crosstab query and want to create
a
crosstab report but following the Help file instruction and just
realized,
the report columns are fixed and not dynamic. Is there any solution
that
will generate the crosstab dynamic column?

I was thinking about dumping the query data directly to an Excel
spreadsheet, if MS ACCESS report don't support dynamic columns...

Thanks

Jean

 

[Duane Hookom]

The link was from a company I worked for almost 5 years ago. They must have
changed direction and removed my examples. I think Roger Carlson has the
same Crosstab example at his site
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.
--
Duane Hookom
MS Access MVP

this link doesn't work, can someone give an alternative link or something
like that?

The site seems to have some issues once in a while. Generally if you wait
a
couple hours, it is back up and running. I no longer work for the company
but did talk with one of the owners last night and he didn't mention
anything about their web presence.
--
Duane Hookom
MS Access MVP

Mr. Hookom,

You and Mr. Browne have been most helpful to me in my efforts to
understand
this and other Access issues. This link intrigues me very much, but it
is
apparently no longer valid. Has it moved to another location?

Jaybird

:

Option 4. Use the method in the Crosstab.mdb demo found at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. This solution is
very
efficient, uses less code than Option 2, and is much more flexible.

--
Duane Hookom
MS Access MVP


As you found, the report does not cope with column names that come
and
go
as they do in a crosstab query.

Some options:

Option 1. Specify the column names in the query's Column Headings
property.
If the column names are fixed (though not necessarily always
present),
you
can type them into the Column Headings property of the query
(Properties
box, in query design view.) For example, if the your column heading
field
is:
Month([SaleDate])
but there may not be any records for month 12 until the end of the
year,
you could enter all the numbers in Column Headings.

Option 2. Save the report with unbound controls, and assign in
Report_Open.
You can save the report without enough text boxes to cope with the
maximum
number of columns you will ever need. The text boxes are unbound,
and
the
report's RecordSource is blank.

Then in the Open event of the report, generate the crosstab query
statement dynamically. Assign this string to the report's
RecordSource,
and assign each of the column heading fields to the Control Source
of
the
appropriate text box. Set the Left and Width of each box, and the
Caption
of the label above the column, and hide the unused text boxes and
labels.

This takes a bit off effort, but does produce very flexible reports.

3. Option 3. Build the report dynamically.
It is possible to CreateReport() and CreateControl(), setting it up
for
whatever columns you determine you need at runtime.

I do not recommend this 3rd approach, as a) it is more complex; b)
it
does
nothing that option 2 cannot do; c) it stops you generating an MDE;
d)
it
bloats the database; e) it leaves the user with questions about
saving
the
report when they close it.


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.


I am using MS ACCESS XP. I setup an crosstab query and want to
create
a
crosstab report but following the Help file instruction and just
realized,
the report columns are fixed and not dynamic. Is there any
solution
that
will generate the crosstab dynamic column?

I was thinking about dumping the query data directly to an Excel
spreadsheet, if MS ACCESS report don't support dynamic columns...

Thanks

Jean

 

[Nobleman]

Hi Duane,
This site pops up a lot but I don't see any file on this site. Do
you know if it exists anywhere else?
Rgds
Nobleman

Option 4. Use the method in the Crosstab.mdb demo found at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. This solution is very
efficient, uses less code than Option 2, and is much more flexible.

--
Duane Hookom
MS Access MVP

As you found, the report does not cope with column names that come and go
as they do in a crosstab query.

Some options:

Option 1. Specify the column names in the query's Column Headings
property.
If the column names are fixed (though not necessarily always present), you
can type them into the Column Headings property of the query (Properties
box, in query design view.) For example, if the your column heading field
is:
Month([SaleDate])
but there may not be any records for month 12 until the end of the year,
you could enter all the numbers in Column Headings.

Option 2. Save the report with unbound controls, and assign in
Report_Open.
You can save the report without enough text boxes to cope with the maximum
number of columns you will ever need. The text boxes are unbound, and the
report's RecordSource is blank.

Then in the Open event of the report, generate the crosstab query
statement dynamically. Assign this string to the report's RecordSource,
and assign each of the column heading fields to the Control Source of the
appropriate text box. Set the Left and Width of each box, and the Caption
of the label above the column, and hide the unused text boxes and labels.

This takes a bit off effort, but does produce very flexible reports.

3. Option 3. Build the report dynamically.
It is possible to CreateReport() and CreateControl(), setting it up for
whatever columns you determine you need at runtime.

I do not recommend this 3rd approach, as a) it is more complex; b) it does
nothing that option 2 cannot do; c) it stops you generating an MDE; d) it
bloats the database; e) it leaves the user with questions about saving the
report when they close it.


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am using MS ACCESS XP. I setup an crosstab query and want to create a
crosstab report but following the Help file instruction and just
realized,
the report columns are fixed and not dynamic. Is there any solution that
will generate the crosstab dynamic column?

I was thinking about dumping the query data directly to an Excel
spreadsheet, if MS ACCESS report don't support dynamic columns...

Thanks

Jean

 

[Duane Hookom]

Try this one
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP

Hi Duane,
This site pops up a lot but I don't see any file on this site.
Do
you know if it exists anywhere else?
Rgds
Nobleman

Option 4. Use the method in the Crosstab.mdb demo found at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. This solution is
very
efficient, uses less code than Option 2, and is much more flexible.

--
Duane Hookom
MS Access MVP

As you found, the report does not cope with column names that come and
go
as they do in a crosstab query.

Some options:

Option 1. Specify the column names in the query's Column Headings
property.
If the column names are fixed (though not necessarily always present),
you
can type them into the Column Headings property of the query
(Properties
box, in query design view.) For example, if the your column heading
field
is:
Month([SaleDate])
but there may not be any records for month 12 until the end of the
year,
you could enter all the numbers in Column Headings.

Option 2. Save the report with unbound controls, and assign in
Report_Open.
You can save the report without enough text boxes to cope with the
maximum
number of columns you will ever need. The text boxes are unbound, and
the
report's RecordSource is blank.

Then in the Open event of the report, generate the crosstab query
statement dynamically. Assign this string to the report's RecordSource,
and assign each of the column heading fields to the Control Source of
the
appropriate text box. Set the Left and Width of each box, and the
Caption
of the label above the column, and hide the unused text boxes and
labels.

This takes a bit off effort, but does produce very flexible reports.

3. Option 3. Build the report dynamically.
It is possible to CreateReport() and CreateControl(), setting it up for
whatever columns you determine you need at runtime.

I do not recommend this 3rd approach, as a) it is more complex; b) it
does
nothing that option 2 cannot do; c) it stops you generating an MDE; d)
it
bloats the database; e) it leaves the user with questions about saving
the
report when they close it.


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.


I am using MS ACCESS XP. I setup an crosstab query and want to create
a
crosstab report but following the Help file instruction and just
realized,
the report columns are fixed and not dynamic. Is there any solution
that
will generate the crosstab dynamic column?

I was thinking about dumping the query data directly to an Excel
spreadsheet, if MS ACCESS report don't support dynamic columns...

Thanks

Jean

 

[Ticotion]

Hi Allen

Could you show how the code for option 2 could be written?

thanks

Ticotion

As you found, the report does not cope with column names that come and go as
they do in a crosstab query.

Some options:

Option 1. Specify the column names in the query's Column Headings property.
If the column names are fixed (though not necessarily always present), you
can type them into the Column Headings property of the query (Properties
box, in query design view.) For example, if the your column heading field
is:
Month([SaleDate])
but there may not be any records for month 12 until the end of the year, you
could enter all the numbers in Column Headings.

Option 2. Save the report with unbound controls, and assign in Report_Open.
You can save the report without enough text boxes to cope with the maximum
number of columns you will ever need. The text boxes are unbound, and the
report's RecordSource is blank.

Then in the Open event of the report, generate the crosstab query statement
dynamically. Assign this string to the report's RecordSource, and assign
each of the column heading fields to the Control Source of the appropriate
text box. Set the Left and Width of each box, and the Caption of the label
above the column, and hide the unused text boxes and labels.

This takes a bit off effort, but does produce very flexible reports.

3. Option 3. Build the report dynamically.
It is possible to CreateReport() and CreateControl(), setting it up for
whatever columns you determine you need at runtime.

I do not recommend this 3rd approach, as a) it is more complex; b) it does
nothing that option 2 cannot do; c) it stops you generating an MDE; d) it
bloats the database; e) it leaves the user with questions about saving the
report when they close it.


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am using MS ACCESS XP. I setup an crosstab query and want to create a
crosstab report but following the Help file instruction and just realized,
the report columns are fixed and not dynamic. Is there any solution that
will generate the crosstab dynamic column?

I was thinking about dumping the query data directly to an Excel
spreadsheet, if MS ACCESS report don't support dynamic columns...

Thanks

Jean