Display zero values in a crosstab query

[petewilson44]

Is there any way a crosstab query will display a full table even if there are limited records.

i.e. for a table that holds actions submitted to a certain department - where actions were submitted on mon, tues, wed, fri, sat, but not thursday or sunday

at the moment the crosstab will look like this:

date count
monday 15
tuesday 8
wednesday 6
friday 2
saturday 3

which screws up the report as the template headings display each day

could i get the crosstab to display:

date count
monday 15
tuesday 8
wednesday 6
thursday 0
friday 2
saturday 3
sunday 0

or a variation around this......???

 

[DCrake]

If you go to the properties of the column field that displays the data and select Column Headings then type in the days of the week "Monday","Tuesday","etc" then run the query it will force the headings to appear even iof there is no data present.

David

 

[petewilson44]

ha, strange that you should post that literally 10 seconds after i stumbled upon it whilst searching through the properties.....however.....it gives the headings I want/need, but doesnt display any data!

so i can have data without headings....and headings without data.......maybe i should just send 2 reports every day......as well as a pair of scissors and some pritt stick!!

 

[petewilson44]

no! IT WORKS!!!!!!!!!!!!!!!!!

hmmmm, must have bin me being an idiot......[big sigh].....that has saved me a lot of bodging, fudging, hacking, stressing, explaining, crying, screaming, slapping, sweating and s##tting!!!

cheers guys

 

[namliam]

Crosstabs dont show 0 where there is a NULL value...

What you can do if you have a limited set of fields like this from your crosstab.... is build a query on top of the crosstab replacing null values by 0.

Select NZ(YourMonday,0) as New0Monday
from YourCrosstabQuery

ANother alternative could be to make a dummy table with always 0 in every day, this you then UNION to your original data...

 

[DCrake]

You must have types in the headings incorrectly. cut them out and re run the query and look at how the headings are displayed/formatted and replicate this in your column headings.

 

[petewilson44]

nil valuesa are fine as long as the headings are there - reason being, the report that is generated is a sub report of a bigger, greater, stronger, prettier, fuller-figured, mega-super-duper-wooper report........and i need the headings to line up

thats cool though, nil values are fine, i can live without "zeros" in my life (which coincidentally enough is the name of a gay club in my town) - so i guess that staement has a double meaning

cheers again guys for all your help! give yourselves a pat on the back, buy yourselves an ice-cream and send me the bill!!

 

[DCrake]

If you actually want zeros to appear instead of nothing then wrap Nz([fieldName],0) around your column heading field

 

[METSKER]

You can display zeros!!!

Set up the value field as follows:

Field--- VALUE: IIf(IsNull(Count(

![field]))=True,0,Count(

![field]))

Table--- it will be blank since defined the table in "Field"
Total--- this is important!!! it must be blank!!! because you defined the total in "Field"
Crosstab--- Value