CrossTab Reports

[Guest]

I have a crosstab query, in which the number of columns is determined by the
user. Basically, the rows list the various items and their descriptions, and
the columns show the dates (as below).

Date1 Date2 Date3 Date4 Date5
Item1 30% 10% 30% 40% 30%
Item2 10% 20% 10%
Item3 0% 10% 20%
Item4 40% 20%

I need the user to be able to limit the date range of the query, and
therefore will limit the number of columns. Also the database will grow, and
more dates will be added.

I have created the crosstab query which displays the data i require as shown
above, but to "fancy" it up and make it to be a more readable format, i would
like to create a report based on the query. Unfortunately i cannot seem to
do this as the report fields listed from the query show the individual column
headings from when it was run (the column header i have to assign "Date1"
even though that date may be excluded. Is there anyway i can do this or is
this impossible with Access?

 

[Guest]

Hi Becam,

This can be done using VBA code. I have a sample I'd be happy to send to
you, if you send me an e-mail message with a valid reply-to address. My
e-mail address is available at the bottom of the contributor's page indicated
below. Please do not post your e-mail address (or mine) to a newsgroup reply.
Doing so will only attract the unwanted attention of spammers.

Also, I believe MVP Duane Hookom has a sample available. Start here:
http://www.rogersaccesslibrary.com/OtherLibraries.asp

Look for "Hookom, Duane". Then click on the entry that reads Cross Tab (69
KB) Access 97.


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________