Reporting on a Crosstab Error

Topic

New Reply

Basically the issue i’m having is, that i have a crosstab which displays data dependant on variables.

this is all about clinics, and consultant cancellations.

so one of my crosstabs shows the number of clinics canceled by consultants with 1-5 weeks notice, and only the cancellations made within the last month.

displaceyed a little like this;

Consultant 1 2 3 4 5
MR Doc 1 4 8
Ms Doc 1
Dr Doc 1 1 1 1

Obviously it looks a little better, but i cant put up a screenshot, due to policy.

I created a report (containing 2 subreports, to allow 2 variants of this crosstab to be presented side by side)

But what has happened is that the clinic that was canceled in week 2, has rolled over the 1 month limit, which means in the cross tab there is no longer a “2” column, so the report cant find it, and therefore doesnt run, leaving me with no report.

I don’t know how to fix it, and neither can my colleagues, but basically i need to find a way to have the crosstab display the weekly column, even if it is null, or maybe a wayfor the report to work if “2” isnt there?

Any help would be GREATLY appreciated, Thank you.

Chris.

Reply | Quote

Replies

But what has happened is that the clinic that was canceled in week 2, has rolled over the 1 month limit, which means in the cross tab there is no longer a “2” column, so the report cant find it, and therefore doesnt run, leaving me with no report.

If you look at the SQL for your crosstab query, it probably ends something like “…PIVOT WeeksNotice”.

You need to tell Access all the possible values in WeeksNotice; so Access can effectively reserve a column for them, even if there is no data. Change your SQL to: “…PIVOT WeeksNotice IN (1,2,3,4,5)”

Reply | Quote

You need to tell Access all the possible values in WeeksNotice; so Access can effectively reserve a column for them, even if there is no data. Change your SQL to: “…PIVOT WeeksNotice IN (1,2,3,4,5)”

THIS IS GREAT! Thank you, after reading your suggestion to Christopher, I tried it, and also found out that you can also accomplish the same by putting values, separated by commas, in the field properties for the column heading grouping, in the “column heading” property while in the design grid rather than SQL window — magical!
Pat

Reply | Quote

brilliant! Thank you, sounds quite simple really, going to be kicking myself all day for that one.

thanks again for your help you’ve definatley saved me a headache!

Reply | Quote

brilliant! Thank you, sounds quite simple really, going to be kicking myself all day for that one.

thanks again for your help you’ve definatley saved me a headache!

I once had a physics prof who often used the term “intuitively obvious” when making an assumption or arriving at a conclusion. Well, this ain’t one of those times!

There is no way you would have known about this, so no need to kick yourself. The crosstab wizard does allow you to enter expected values, so you’d never routinely run across it.

I don’t remember where I learned it, but it has saved my butt many times, especially when using a crosstab query as the recordsource for a report (as you found out).

Reply | Quote

A more general solution to this problem is to use a dynamic crosstab report. In this case the column headings in the report are not defined in advance, but set in code when the report is opened.

I attach a demo which is not mine. I got it from the Lounge previously, but posts referring to it seem to have been deleted.
(I could not find any, anyway.)

Reply | Quote

A more general solution to this problem is to use a dynamic crosstab report. In this case the column headings in the report are not defined in advance, but set in code when the report is opened.

I attach a demo which is not mine. I got it from the Lounge previously, but posts referring to it seem to have been deleted.
(I could not find any, anyway.)

It is not the column headings, it is the controlsource for the objects in the detail section. And Access2007 doesn’t like to change the controlsource in the Open statement; no such problem with A2003 and before. So now we have to open the report hidden in design mode, change the controlsource, close it, then run the report.

Reply | Quote

I use this dynamic crosstab report in 2007 a lot without problems. (using mdb file format.)

In this context Column Headings and Control Source are the same thing. The control source of the report controls relate to the column headings in the crosstab query.

Reply | Quote