How to make multiple counts by range in Microsoft Access

I work in an insurance company and sometimes they ask me to create an aging of policies or claims. An aging is the amount of time the policy has not been paid or the amount of time a claim has been open. Most of the time they ask me to separate data in ranges of aging like 0-30 days, 31-60 days.. Etc. There is one easy way to make this in Microsoft Access.



For this example I am going to create a Aging by Adjustor. To do this I am going to make a table with two fields. One field will be adjustorName and the other field is aging which is the amount of days the claim has been open. Save the table as agingReport and don’t create a primary key for this example.



Add some data like this:



Save and close the table then create a query using the query design. Add the adjustorName to the first column



Now (this is where the magic happens) you will make an if statement in the second column so you can obtain the count of claims occurring for an adjustor within a range 0 to 30 days of being opened.

0-30: IIf([claimsAging].[AGING]>=0 And [claimsAging].[AGING]<=30,1,0)

After copying this to the Field: in the second column hit the Totals button on the top and set the totals if the second column to Sum.

What this will do is it will set the aging to 1 if the aging is between 0 and 30. If not it will set the aging to 0. And after setting the value to 0 or 1 it will sum all the value to give a count by adjustor of given range.




After that repeat the process for the ranges of 31-60, 61-90 and Over 90 and it should look something like this:



Or you can use the following query:

SELECT agingReport.adjustorName, Sum(IIf([agingReport].[AGING]>=0 And [agingReport].[AGING]<=30,1,0)) AS [0-30], Sum(IIf([agingReport].[AGING]>=31 And [agingReport].[AGING]<=60,1,0)) AS [31-60], Sum(IIf([agingReport].[AGING]>=61 And [agingReport].[AGING]<=90,1,0)) AS [61-60], IIf([agingReport].[AGING]>=90,1,0) AS [OVER 90] FROM agingReportGROUP BY agingReport.adjustorName, IIf([agingReport].[AGING]>=90,1,0);

Download this Access 2007 file here: DOWNLOAD ME

Note: It is supposed to be adjuster instead of adjustor.. I am not going to do the whole tutorial again for just that :)