A Histogram is essentially a visual representation of a frequency distribution – a kind of analysis that categorizes data based on the count of occurrences where a variable assumes a specified value attribute.
Although most Excel users think of Access as simply a place to store data, I often encourage building analyses, like histograms, directly in Access. I have tons of thoughts on why Access should be incorporated in these types of analyses, but I’ll go into all that later. For now, I’ll show you an easy way to create a histogram in Access.
Step 1: Create the Frequency Distribution
Obviously, the first step in creating a histogram is to build a frequency distribution. In Excel there are several tools provided for this purpose. In Access, you have the PARTITION function.
The PARTITION function “identifies the particular range in which number falls and returns a Variant (String) describing that range.”
There are four required arguments: PARTITION(Number, Range Start, Range Stop, Interval)
Number: The number you are evaluating.
Range Start: The start of the overall range of numbers.
Range Stop: The overall range of numbers.
Interval: The span of each range in the series from Range Start to Range Stop.
So step one is to create a Query that applies the PARTITION function appropriate to your analysis.
As you can see in the query shown here, we are using the Partition function to evaluate the Revenue field, starting the series range at 500, ending the series range at 100,000, and setting the range intervals to 5,000.
Running this query, allows us to analyze how many employees fell into each range of dollar sales. As you can see, the Partition function gives us a relatively easy, one-query, method of creating a frequency distribution.
Step 2: Switch to PivotChart View
While in query Design View, go to the Design tab and select View – > PivotChart View.
Within a few seconds, Access will run your query and return the results into chart form.
I would like to do this based on Number of Prescriptions by doctor into 10 buckets. The first decile would contain the highest prescribing physicians that make up 10 % of sales. Each decile would contain the number of physicians that make up the next 10% of sales. Is there a way to do this? Thanks.
Hi Christine,
You would have to build a query that gives you percentile ranking. A simple Histogram probably wouldn’t do it for you.
I explain this in my “Access Data Analysis” book in the ‘Running Descriptive Statistics’ chapter.
I you send me a sample of your database, I can get you started.
Hi,
Is there a way to change the axis maximum value? For example, the bins are 0-9, 10-19, but I only have a count for values between 60:69. But I would like to show the other intervals up until 100.
Thanks!