Access partition function: Is there a way to make it show bin categories that don't have a count?

Question

Hi,

I'm trying to use the Access Partition function to generate the bins used to generate a histogram chart to show the frequency distribution of my % utilization data set. However, the Partition function only shows the category bin ranges (e.g. 0:9, 10:19 etc) only for the categories that have a count. I would like it to show up to 100.

Example: Using this function:

% Utilization: Partition([Max],0,100,10)

The Full SQL is:

SELECT Count([qry].[Max]) AS Actuals, Partition([Max],0,100,10) AS [% Utilization]
FROM [qry]
GROUP BY Partition([Max],0,100,10);

gives me:

Actuals  |  % Utilization
4        |   0:  9
4        |  10: 19
4        |  20: 29 

but I want it to show 0s for the ranges that don't have values up to 90:99. Can this be done?

Thanks in Advance

Answer 1

The only way I can think of doing this is with an additional Bins table that contains all the bins you wish to illustrate:

SELECT Bins.[% Utilization], t.Actuals FROM Bins
LEFT JOIN
     (SELECT Count(max) AS Actuals, 
             Partition([max],0,100,10) AS [% Utilization]
      FROM qry
      GROUP BY Partition([max],0,100,10)) t
ON t.[% Utilization]=bins.[% Utilization]