# Histogram Query

Access Histogram query example – you’ll be surprised at how easy it is to do the calculations for a seemingly complex statistical measure using a single Access query.  In this example we have customers who purchased items from a store. We want to know the distribution of customer purchases grouped by customer’s age.   To start, we have a table called M_Customer_Purchases (in reality this would probably be a query based on a Customer table and a Purchases table).

The table has 2 fields (the purchase_date field is really not required for this example).

Age
Purchase_Date

The following sql statement will group the ages (purchases) into 6 categories based on the customer’s age.  You can see the bracketing of the age in the immediate if statement (iif) – this created the Access histogram values.

SELECT
Sum(IIf([Age]<18,1,0)) AS Group1,
Sum(IIf([Age]>=18 and [Age]<30,1,0)) AS Group2,
Sum(IIf([Age]>=30 and [Age]<40,1,0)) AS Group3,
Sum(IIf([Age]>=40 and [Age]<50,1,0)) AS Group4,
Sum(IIf([Age]>=50 and [Age]<60,1,0)) AS Group5,
Sum(IIf([Age]>=60,1,0)) AS Group6
FROM M_Customer_Purchases;

The following is the result of the Access example query.  You could easy pass this query to a bar chart to display the results graphically.

Note the trick is the 1, 0 in the iif statement – when the age matches the age range in the iif statement then the result is one, otherwise the result is 0, thereby providing a way to SUM the results.

You should be able to see how you can bracket results for all kinds of data.  You could bracket date ranges to simulate a crosstab query (pivot query).  One advantage of using the method in this example over crosstabs is that you can pass query parameters to the query from a form field.

You can combine this query technique for histogram calculations with our bar chart example.  The code in the bar chart example will work with forms or reports.