How to get all Hours as a result of this query and put it in a graphs

[DaOtH]

All,

I have the following problem...

I am running the following query :

SELECT Items.Type, Hour([Time]) AS [Hour], Sum(Sales.Qty) AS Qty
FROM Sales INNER JOIN Items ON Sales.Item = Items.Item
GROUP BY Items.Type, Hour([Time]);



Result of this query is as follows :

Type1 Hour1 Qty
Type1 Hour2 Qty
Type2 Hour1 Qty
Type2 Hour2 Qty

Etc.

I use this to put the sales / hour / type in graphs.
Problem however is that i need to fix the x-axis of the graph in that way that i alwa's get the same format (e.g. all hours, even if not given a result in the query)

So, the query can come back with the following hours :
Type Hr Qty
Type1 00 10
Type1 01 15
Type1 02 8
Type1 08 30
Type1 14 50

But i need it to return
Type Hr Qty
Type1 00 10
Type1 01 15
Type1 02 8
Type1 03 0
Type1 04 0
Type1 05 0
Type1 06 0
Type1 07 0
Type1 08 30
...
Type1 23 0


Anybody any idea on how to do this

I worked it out using another table containing all possible hours and an inner join, that works fine. However, i can't have extra queries / tables in the database. This query needs to be directly in the graphs data source. "In three words I can sum up everything I've learned about life: it goes on."
- Robert Frost 1874-1963

 

[MichaelRed]

Actuall, I prefer the technique of having the 'required' fields in the join, as you have done, but there is a properrty in the crosstab query (Headings?) which you CAN use to achieve the same effect -as long as the fields are TRULY static.

You mention that you cannot have extra tables / queries in the db, but do not mention any reason for such a (severe) restriction. Perhaps you shoud discuss this with the "powers that be" and let them understand that the restriction is quite likely to prevent you (and others) from achieving the desired functionallity and will certainly increase the effort necessary to provide the functionallity.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over