Crosstab query & report 3

[wuhair]

Hello,

I'm using Access 2000 and don't have any other software.

I have a working cross tab query where i have 2 different ranges for column heading and row headings. To simplify lets say:

A-C D-F Total
1-5 10 10
6-10 20 30 50

Lets say my table looks like:

ID AlphaType NumberType Value AlphaRange NumRange
1 4 A 10 A-C 1-5
2 6 B 20 A-C 6-10
3 7 D 30 D-F 6-10

So i want my report to look like:
A-C D-F Total
1-5 10 10
6-10 20 30 50
Total 30 30

My problem is i will be running different data tables through this, I can't get the report to work in a dynamic way like if I don't have any "D-F" values on the next table, that column won't be there and it should look like:

A-C Total
1-5 10 10
6-10 20 20
Total 30

Thanks for any help. I have been searching everywhere. And i can understand basic vba code. thanks

 

[CosmoKramer]

Are you trying to say that you want the "D-F" value to appear as a crosstab column, even if no "D-F" records exist???

 

[wuhair]

no i don't want d-f to appear if no values are present. just like how the query would run.

 

[CosmoKramer]

If you already have a working crosstab query, why can't you create a report with that query as its RecordSource??

 

[wuhair]

Cosmo Kramer,

Could you tell me how that is done? i'm not familiar with RecordSource. Also would this work even if the data being queryied changes? thanks

 

[CosmoKramer]

The easiest way is to use the Report Wizard function. In the New Report dialog box click on Report Wizard and select your crosstab query in the drop-down box. Move all of the available fields to the selected fields box and click Next. Click Next to bypass grouping, and again to bypass sorting. Select Tabular layout and whatever orientation and style you want, and that should do it....

 

[wuhair]

oh i tried that but the problem is that it's not dynamic. so when i have different data next week. i'll have to redo the report everytime.

 

[CosmoKramer]

OK, now I get it.

Do you have the Solutions.mdb provided with Access? If so, there is an example under "Create a Crosstab Report with Dynamic Column Headings". It looks pretty involved, but it lists step-by-step what you have to do. Here is the overview:

Create a crosstab query to use as the record source for the report. In report Design view, create unbound text boxes in the page header (for column headings), in the detail section (for the row heading and crosstab values), and in the report footer (for column totals). In the OnFormat properties of the page header and detail sections, create event procedures that fill the text boxes with the column headings and crosstab values and hide the unused text boxes. In the OnPrint property of the detail section, create an event procedure that calculates the row total and adds it to the column-total array. In the OnPrint property of the report footer, create an event procedure that fills the text boxes with the column totals.

I hope this helps.....

 

[dthakkar]

HI in this thread Cosmokramer mentioned:

"Are you trying to say that you want the "D-F" value to appear as a crosstab column, even if no "D-F" records exist???"

I have a similar problem. I am trying to make a quarterly report and i want 0 to appear if there we no sales in that category for that quarter. can anyone please tell me how to do it.

Thanks
Dharmesh

 

[CosmoKramer]

You could use the In clause as part of your PIVOT statement to do two things:

* Define values for columns so that columns will appear even when there is no data for them
* Define the order of the columns

Something like this, of course changing to your names:

PIVOT [CrossTabQuery].[Quarter] In ('Qtr1', 'Qtr2', 'Qtr3', 'Qtr4');

 

[dthakkar]

Hi,

Thanks for the instant reply. But what if the combinations for which the data doesnt exist are rows. After looking at your post i went back to the SQL view of the crosstab query and understood where you are talking about adding columns.

But then in the output that i see now from the query there are also rows which do not have the data. suppose there is a category for which there was no sale for any quarter that wont appear in the output spreadsheet at all. can I do something about that.

Thanks
Dharmesh

 

[dhookom]

If you want rows that have no data, you must include a table or query in your crosstab that does include all the unique rows. Use a join that includes all the records from your complete list and use the complete list field as your Row Heading.

For the record, the Solutions.mdb solution is slow and inflexible. For a faster and more flexible solution that allows for any number of "columns", try the sample report from

http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

Duane
MS Access MVP