Re: Crosstab Report

Take a look at this solution
http://www.access.hookom.net/DynamicMthlyCrosstabRpt.htm.


--
Duane Hookom
MS Access MVP

"Frank" <Frank@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B0C3DF18-14AE-4029-A5A6-13AEF32F452D@xxxxxxxxxxxxxxxx
The column headings are generated using a crosstab query. We have about
12
years of data beginning in 1994. The user wants to see three years at a
time
but wants to choose which years to use. I can do that in the query but
when
I try to write the report my headings/totals crash.


"Duane Hookom" wrote:

Can you provide more details? How are your column headings generated?

--
Duane Hookom
MS Access MVP

"Frank" <Frank@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4CFC8D2F-C8AF-4CEC-950B-68CB045CA560@xxxxxxxxxxxxxxxx
Thanks for the information about dynamic column headings. I was able
to
view
some of the information you posted to the query and found it very
useful.
The problem I'm having is similar. I'm working on a trending report
and
would like to choose the year the report starts. Any suggestions?

Thanks

Frank Bobak

"Duane Hookom" wrote:

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

--
Duane Hookom
MS Access MVP

"Nobleman" <Nobleman@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:45D3D234-C799-43EF-AB1C-86489D8F56C8@xxxxxxxxxxxxxxxx
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

"Duane Hookom" wrote:

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


"Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
news:OA3ZiFILGHA.3492@xxxxxxxxxxxxxxxxxxxxxxx
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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jean" <jean@xxxxxxx> wrote in message
news:eb3I9vHLGHA.3396@xxxxxxxxxxxxxxxxxxxxxxx

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
spread***, if MS ACCESS report don't support dynamic
columns...

Thanks

Jean













.