Report based on a crosstab query 1

[Ymesei]

Crosstab queries are great.&nbsp;&nbsp;But can you base a report on a crosstab query where the field names created by the crosstab are not static?<br><br>Here's my situation.&nbsp;&nbsp;I have a crosstab query that groups data from a three month period into three columns like this:<br><br>Agent&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;05/00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;06/00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;07/00<br><br>Ed&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;15&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;20<br>Tom&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;12&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;17<br><br>The last three field names are dependant on what three month period the user identified for the report.&nbsp;&nbsp;How can I get a report to show this data in the same format as the query?

 

[Quehay]

This isn't as pristine as you might want, but what about throwing the query results into Excel and formatting there?&nbsp;&nbsp;For crosstabs this is a good viewing/reporting mechanism (I put a lengthy code swatch in the VBA forum re: sending a query to Excel and specifying where the printing occurs to allow insertion into an existing template).

 

[Ymesei]

Thanks for the input Quehay.&nbsp;&nbsp;Actually, I was trying to avoid having to have to do that.&nbsp;&nbsp;I'm trying to keep the database as user friendly and &quot;Point & Click&quot; as possible for the end users.<br><br>Actually I answered my own question.&nbsp;&nbsp;For anyone else whose been suffering from sleepless nights trying to figure out how to do dynamic columns in access reports, try the help files.&nbsp;&nbsp;<br>Look in: &gt;Working with Reports&gt;Advanced Reports&gt;Crosstab Reports&gt;Example of a crosstab report with dynamic column headings.<br><br>It's quite a bit of code, but I'd say the result is well worth it.

 

[errolf]

Dear Ymesai where abouts is the resource that you refer to in your post regarding dynamic column headings
Regards errolf

 

[Ymesei]

Errolf,

Access 97 comes with two sample databases that you have the option to include when installing Access 97. The one most people have probably seen is the Northwind database. There also is another one called Developer Solutions. If you install the sample databases, it can be found in C:\Program Files\Microsoft Office\Office\Samples\Solutions.mdb, assuming of course you installed in the default location.

Open up this database and you'll find a slew of advanced access samples. If you select sample reports, and choose the Employee Sales report, you'll see an example of a report based on a crosstab query with dynamic column headings. Be sure to check the option to automatically view the Show Me help window and you'll get a great help dialog with step by step instructions on how to set up such a report and what code you'll need to write.

Like I said, it's a much longer process than I had originally assumed was necessary, but the end result is a great looking report that shows data in the exact same format as if you were viewing a crosstab query's output.

Hope this is helpful for you.

Will

 

[errolf]

Thanks for your prompt reply, Access 2000 does not come with this Solutions Database and as usual the code in Access DAO's has changed keywords so in order to get the code to work a lot of the the keywords need changing .

Thanks for your info
errolf

 

[Ymesei]

Errolf,

That's too bad. Since I found out about the Developer Solutions sample, I've found that its a great resource to learn about some of the more difficult/advanced capabililities that Access 97 has. Anyway, I understand that Access 2000 has some different code than 97, but if you like, I could email the help dialog and the code it came with in Word format for you to look at and maybe try to get a feel for what's necessary to get a report like that to run in 2000. Just shoot me an email and I'll send you the file.

Will
wymesei@ite.net

 

[Hokis]

The solutions database for Access 2000 can be downloaded from the microsoft site. I found the address in the help files but can't remember where. I think I searched help for

 

[Hokis]

I think I searched help for &quot;sample database&quot; Search for solutions didn't seem to work

 

[maria28gr]

I have already given an answer to this problem.
Come to

http://www.tek-tips.com/gviewthread.cfm/lev2/4/lev3/27/pid/703/qid/35663

to view.


Maria
maria.karakiza@usa.net