Crosstab Query in a report

[lemon_balm]

Hi

I am trying to build a crosstab report which generates the columns from a query on the fly.
THe reason for this is to record any issues we have on each day so for example

We first call the report on the 1st of March and we had

Issue----------------Total Issues-----01/03/06
Printer breakdown---------3--------------3---

And on the 3rd of March the report should read

Issue----------------Total Issues----01/03/06----03/03/06
Printer breakdown---------5-------------3------------2---
Oil Spill-------------------2--------------------------2---

I have looked at all the posts to do with this on the forum and they all recommend looking at Solutions.mdb which is supplied with access. Unfortunately, not only has our IT department failed to install this document, I am also running Access 97 and all the copies on the web seem to be for Access 2000.
Can anyone help me please?

Jason

 

[lemon_balm]

Please help...

Has anyone got a copy of the solutions.mdb for Access 97 or a Access 2000 version manipulated for Access 97 as I really need to get going on this.

If anyone has any ideas on how to make this work I would greatly appreciate it.

Regards

Jason

 

[lemon_balm]

Crosstab Issues

Ok, it took me a while but I found a copy of the solutions.mdb for Access 97. However, after poring over it for a good few hours, I cannot figure out how to convert it for what I require.
Maybe it's just me but the crosstab report they have created in solutions.mdb is full of extra code for other things.
Could somebody please help me figure out the parts I need and parts I don't. All I want is to press a button and a corsstab report is created on the fly to allow for the most current data to be added. (I.e. the latest date as a column)
With the solutions.mdb, they have a splash screen for selecting lots of various criteria first and I do not need this. Looking at the code though, it seems almost integral to the report working so I cannot figure which parts I can replace and which I can't.

Regards

Jason

 

[JoeCruse]

Just a suggestion, but it might make it easier if you reverse the way you display the data. Especially if you have a set group of issues that are counted. Then each row represents a day, and the columns are issues. Of course, your business rules may not allow for the report being displayed that way.

The dynamic report solution is a little complicated. If you have a set group of issues, you could set them as column headers in the underlying query, thus making the crosstab report MUCH easier to do.

 

[lemon_balm]

Hi Joe

Thanks for the reply.
I tried reversing the query as suggested to have all my issues (63 of them) across the top of the page. Unfortunately I cannot get all the issues to display. If there is no value for them, then they don't come up. I would prefer them to come up as a collection of zeroes but this doesn't seem to happen. Any suggestions on how this can be achieved? My other worry is that if I add another issue, I will have the same problem I was having with the dates and have to completely rebuild the report.
Has anyone actually created an "on the fly" report at all that I could view the code for as this is probably going to be the best solution. (I just need to get my head around the coding)

Regards

Jason

 

[JoeCruse]

I've never tried what you need, as far as showing a zero when there is a null, but this has been discussed here before. Search on showing zero for null.

It looks like you will HAVE to go with the dynamic report in the Solutions app. It is not easy to understand, but it WILL work for your situation. It was developed with that problem in mind. I worked it up for a similar situation I ran in to. Unfortunately, in my case it ran VERY slow. I found a workaround thanks to Access MVP Duane Hookum at the Tek Tips forum, but I do not think it would work for your situation.

 

[lemon_balm]

Nearly There

I've finally created a working 'on the fly' report.
Unfortunately the report only shows one line of the setail section and I cannot figure out why.
Can someone please have a look at the code for me as I am starting to pull my hair out.
I've got so far and then suddenly it fails at the last hurdle
THe db is in Access 97 format.

regards

Jason

 

[mboe]

Did you ever fix the problem with only one line of data showing? I need to build a similar report.

Mike

 

[lemon_balm]

mboe

Unfortunately, I couldn't fully work out how to write the code.
Instead, I left the Excel Output in and use that instead. It is accurate, saves automatically to your My Documents folder and is printable from there.

Regards

Jason

 

[lemon_balm]

Crosstab report

Ok, to get into the backend, hold shift whilst opening the database.

To find the code, have a look at the code behind the Summary Report button on frmoutershell and the Output to Excel button on setdates

Hope this helps

Regards

Jason