Re: Crosstab Report

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










.