crosstab query

[Dgavilanes]

Hi All;
A report based on a crosstab query that when run it shows how many employees type (T, A, E) are on l conference leave on a particular date.
my problem is that the report only runs when all three types are present, if one type is missing in a particular report date. It just does not run.
Is there a way to make the report run and show (0) as a default for the missing letters


thanks

Dennis

 

[llkhoutx]

The recordsource of your crosstab query should be your regular table or query Unioned with a table containing zero values for all fields, then all columns always appear.

 

[uncle-lai]

Dynamic Crosstab Report

I had spent a lot of time in searching for better ways to display two dimentional data by forms or reports. I tried to used Crosstab Queries and Pivot Table Forms too, but there was always some problem. Here is some useful information about Crosstab Query, if you find that cannot solve your problem take a look at my Dynamic Crosstab Report sample. Hope you can find some hints here.

http://support.microsoft.com/default.aspx?scid=/support/access/content/nwind/t-art_083.asp

Peter

 

[Dgavilanes]

Re:Crosstab

Hi Peter, and uncle-lai
Thanks for your quick replied, I created a new table
Type* (Primary key) =t, A, E
Val, number

I linked it to the recordset in the query, I'm still getting the fields that are current ( A, T) E is missing because there was no entry for this type.

I know I'm doing something wrong


Thanks

Dennis

 

[uncle-lai]

Hi Dennis,

Are the fields "T", "A", "E" Column Headings of your crosstab query? I believe they are fixed Headings. If yes then open the Property page of your query and enter this line in the Column Headings field:

"T", "A", "E"

The Headings will appear in the same order as you entered.
For more information :

http://support.microsoft.com/default.aspx?scid=/support/access/content/nwind/t-art_083.asp

You might not need the new table "Type".

Peter

 

[Dgavilanes]

Re:crosstab

Hi uncle-lai


It works great, I pasted it in the column properties and it works perfect.
thanks so much

have a nice weekend


Dennis

Chicago

 

[Dgavilanes]

RE: crosstab

Hi uncle-lai


It works great, I pasted it in the column properties and it works perfect.
thanks so much

have a nice weekend


Dennis

Chicago