Prevent gaps in Partition function results

[iCarbon]

I am using the partition function to generate data that I then use for a histogram chart. I am summing the quantity of parts and grouping by part size. Pretty much everything works, except for missing ranges in the partition output.
If I have no data records within a partition range, then that range is not included in the results. I want the range listed, but with a zero in the quantity column.
For example: if I use partition([size], 71, 100, 10) and there is no record with a size between 80 and 90-

I get:
71:80 | 15
91:100 | 23

I want:
71:80 | 15
81:90 | 0
91:100 | 23
​

I've tried to work around this by adding records to fill the gaps with a zero quantity, but this "solution" is fragile and does not adapt well to other changes.

Anyone with a good idea?

 

[Guus2005]

Create a lookup table (tblRange) to store all available ranges.
Left or Right Outer Join this table to the actual data. Now you'll get the records from the tblRange table even when there is not a matching record in the data table.

HTH

 

[iCarbon]

Thanks for the suggestion. I may be doing something wrong, but could not get the left-join type query to work as desired. Ended up creating a table and an associated query to generate all possible range values. Then did a UNION query with the actual data query.

I can then work on the result and that delivers all ranges as desired. Not ideal, but cannot think of a better way.

The data table contains several (unmentioned in the original post) fields I use for grouping and sorting. It is necessary to generate range values for all group permutations that I want to use. Luckily the group ranges are small, 6 to 30 values.