Re: Crosstab Report
- From: Frank <Frank@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 21 Sep 2006 08:26:01 -0700
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
spreadsheet, if MS ACCESS report don't support dynamic columns...
Thanks
Jean
- Follow-Ups:
- Re: Crosstab Report
- From: Duane Hookom
- Re: Crosstab Report
- Prev by Date: Re: Field Display Question
- Next by Date: Re: If null in textbox
- Previous by thread: Re: Field Display Question
- Next by thread: Re: Crosstab Report
- Index(es):
Relevant Pages
|
