×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Report based on a crosstab query

Report based on a crosstab query

Report based on a crosstab query

(OP)
Crosstab queries are great.  But can you base a report on a crosstab query where the field names created by the crosstab are not static?

Here's my situation.  I have a crosstab query that groups data from a three month period into three columns like this:

Agent     05/00     06/00     07/00

Ed          15        10        20
Tom          7        12        17

The last three field names are dependant on what three month period the user identified for the report.  How can I get a report to show this data in the same format as the query?

RE: Report based on a crosstab query

This isn't as pristine as you might want, but what about throwing the query results into Excel and formatting there?  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).

RE: Report based on a crosstab query

(OP)
Thanks for the input Quehay.  Actually, I was trying to avoid having to have to do that.  I'm trying to keep the database as user friendly and "Point & Click" as possible for the end users.

Actually I answered my own question.  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.  
Look in: >Working with Reports>Advanced Reports>Crosstab Reports>Example of a crosstab report with dynamic column headings.

It's quite a bit of code, but I'd say the result is well worth it.

RE: Report based on a crosstab query

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

RE: Report based on a crosstab query

(OP)
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

RE: Report based on a crosstab query

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

RE: Report based on a crosstab query

(OP)
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

RE: Report based on a crosstab query

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

RE: Report based on a crosstab query

I think I searched help for "sample database" Search for solutions didn't seem to work

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close