Report with time brackets (some without data)

[charlie442]

Hi

Here is my problem:

I have written a query which brackets turnaround times. The brackets in days are : 0-1, 1-3, 3-5 and so on. This is done in a select query where a calculated field contains the brackets as above. I then run a crosstab query on this query making the brackets field the column heading.

The queries can look over any date range (with dates chosen from a form). Given this there are instances when my query will not have values in any particular bracket.

So say the query returns records in the 0-1 and the 3-5 brackets but not in the 1-3 bracket. The cross tab query will therefore not have a column called "1-3". Thus my report goes haywire because when it is run for differing time periods there is sometimes data in some brackets and sometimes not. (I hope all this makes sense)

What I would like to know is is there a way to have all the brackets always shown on the report and if the query does not find data in one or more of the brackets it will simply not show any value there?

Any help most appreciated
Thanks

 

[plog]

Yes, create a table with a field to hold all the bracketed values you want, then in the crosstab query use that table and LEFT JOIN (include all values from the new table) the sub-query to the field with all the bracketed values.

 

[charlie442]

Thank plog

Sometimes the simplest solutions are the most elegant yet so damn hard to see.

Much appreciated fella

 

[vbaInet]

I can see that plog has given you a good solution.

This is fyi. In a Crosstab query, you use the PIVOT statement to set fixed column headings. So in your case it would be:

PIVOT [FieldName] IN ('0-1', '1-3', '3-5')

There is even a Column Heading property of the Crosstab Query which you can use.