How can I summarize quantities by ranges in Access 2010?

August 1, 2014 12:06 PM

I am trying to summarize a table of thousands of values into simple year ranges. It should be simple. So basically, there is a table that looks something like this: PIPE_PLR_Y MILES 1912 0.06 1914 0.48 1930 1.43 1942 1.32 1929 0.85 1925 2.00 1914 0.95 1929 0.42 ... The table has many, many values between about 1900 and today. I would like to make a table like this: Year_Range MILES 1900 - 1909 23.6 1910 - 1919 64.3 1920 - 1929 73.1 ... Again, I'm using Access 2010. Any help would be massively appreciated.
posted by rybreadmed to Computers & Internet (4 answers total)

Add a column to the table called 'Range'
Run a series of update queries (one per range) to update the value of that column to the range of each record. For example, set 'Range' = 1900 - 1910 when year is between 1900 and 1910.
Summarize by the 'Range' column.
posted by soelo at 12:12 PM on August 1, 2014

Best answer: You want to use the Partition function. Ultimately, I suspect, you're trying to create a histogram.
posted by jedicus at 12:21 PM on August 1, 2014

Crosstab query?
posted by bleep at 12:24 PM on August 1, 2014

Response by poster: Jedicus: You're the best. THE BEST!!! Thank you so much.
posted by rybreadmed at 12:30 PM on August 1, 2014