How to group records into quartiles in Access 2010

I want to assign quartile values to records as shown in the attached file using Access 2010.  The values to be used for this are shown in column D (percmedian), and the process must be repeated for each area.  The attached file is only a sample; there are almost 280 areas and this process will be repeated multiple times for different fields in other tables.  Some areas only have one or two records and I recognize those cannot be used.  For now I just need to see how to create the process for one table.

I would like to populate the Quartile field (column E) with values of Q1, Q2, Q3, and Q4 (those with percmedian values > Q3).  I can do this once I know how to calculate the values for each quartile.

I recognize this will probably be a multi-step process and would prefer to do it using update queries, but using VBA is fine as long as the code is not unusually long or complex.  Any suggestions would be appreciated. 2012-CensusTractsByQuartile-Sample.xlsx
Liberty4allRetiredAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
You could have searched for this:

Quartiles in Access

/gustav
Hamed NasrRetired IT ProfessionalCommented:
Assume your table named quartiles.

Check this select query: (A one step process, may change to update)

SELECT quartiles.State, quartiles.Area, quartiles.percmedian, DCount("Area","quartiles","State='" & [state] & "' And area='" & [area] & "'") AS tot_count, DCount("Area","quartiles","percmedian<=" & [percmedian] & " And State='" & [state] & "' And area='" & [area] & "'") AS seq_count, 100*([seq_count]/[tot_count]) AS ratio, Switch([ratio]<=25,"Q1",[ratio]<=50,"Q2",[ratio]<=75,"Q3",[ratio]<=100,"Q4") AS q
FROM quartiles
ORDER BY quartiles.State, quartiles.Area, quartiles.percmedian;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Liberty4allRetiredAuthor Commented:
Thank you both for responding.  This is exactly what I need.  I did a search on Experts Exchange before posting my question but did not find what Gustav provided.  The query provided by hnasr is very easy to use and is essential.  I seldom receive code that works without having to modify something.  No modifications were required other than the obvious change in table name in the query provided by hnasr.

This will help me accomplish much more than what was mentioned in my post.  I'm very grateful for your time in responding.

Bryan
Hamed NasrRetired IT ProfessionalCommented:
Welcome!
Gustav BrockCIOCommented:
You are welcome!

/gustav
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.