Crosstab report

[ruibaeta]

Hello,

How to design a report bounded to a crosstab query in a MS Access database?

The problem is that a crosstab query has an indefinite number of columns, because this depends on data actually stored in the database.

If I fix the number of columns in query, I wouldn't probably get all data!

Can anyone help me?

[Forest Dragon]

Making a Crosstab report in Access is very hard. I have done it once, using a lot of code lines (in the report class module).

I can give you the steps I used, although I don't think this is the best way to do that, but I don't have a better way:

1. Place the textboxes which will be used to display the data in the columns of the Crosstab query and labels that will be used as the titles of those textboxes. Place them in the maximum amount you can get from the query (I hope you know it).

2. Set the Visible property of all of them to False.

3. Give them names like "Title1", "Value2" and so on. It's important that all of the labels will have the same name with just the number different and so the textboxes.
This is set automatically by Access, but maybe you would want to give them other names.

4. Now it's the hardest part. You will need to write code that opens the recordset of the CrossTab query, reads all the fields, sets one of the textboxes' Source property to every column field and its label's Caption property to that field's name. You can use a loop to do that because of the names you gave the textboxes and the labels in step 3 (by using the names you can discover what the next textbox-label pair that you haven't already used is).

I have written this kind of report at work and I am not sure if I have the source code at home. If you want it, inform me and I will do a search for it in my hard-disk drive.

As I said previously, doing such reports with Access is a very hard process. I suggest doing it with Crystal Reports or maybe some other software program.

Good Luck!!!