# Dividing data into quartiles

Greetings.  I'm trying to come up with a way to divide the records of a particular table into quartiles, based on the ranking of "TotalField."  Specifically, I'd like to set a "QuartileRank" field to either 1, 2, 3, or 4, depending on whether "TotalField" falls within the first (top 25%), second (etc...), third, or fourth quartile of all values in the table.  Anyone have any thoughts on this?

Thanks!!
###### Who is Participating?
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.

Commented:
lets see.. if the Table is ordered on totalfield then RecordCount/4 gives the number of records in each quarter... but then, not all counts are evenly divisible by 4 so you will have 3 mathcing counts and the forth may have an extra bit.. it would be pretty simple in VBA - that ok?

qryCutInQuarters:
--------------------
Select * FROM tblCutMeUp ORDER BY TotalField;

VBA Module
--------------
Dim Db
Dim Rs
Set Db = CurrentDb()
Set  Rs = Db.Recordset("qryCutInQuarters")
Rs.MoveLast
iCnt = Rs.RecordCount
Rs.MoveFirst
iQuarter = Int(iCnt / 4)
For i = 1 to 4
For x = 1 to iQuarter
Rs.QuartileRank = i
Rs.MoveNext
Next x
Next i
Do Until Rs.Eof
Rs.QuartileRank = 4
Rs.MoveNext
Loop

Not sure this is completely error free had a few beers... but it might be :=)

Now that i think about it you could maybe use:
Select TOP 25% * INTO tblQuarter1 FROM tblCutMeUp ORDER BY TotalField;
DELETE TOP 25% FROM tblCutMeUp ORDER BY TotalField;
Select TOP 25% * INTO tblQuater2 FROM tblCutMeUp ORDER BY TotalField;
DELETE TOP 25% FROM tblCutMeUp ORDER BY TotalField;
Select TOP 25% * INTO tblQuater3 FROM tblCutMeUp ORDER BY TotalField;
DELETE TOP 25% FROM tblCutMeUp ORDER BY TotalField;
Select * INTO tblQuarter4 FROM tblCutmeUp ORDER BY TotalField;

as long as you had a copy to work with. Im sure theres a nested solution but i'm not up to it at the moment :)

John

John
Commented:
John
I think the basic SQL approach is OK but you need to reconsider the % values after each deletion.

Pete

Commented:
whoops, my bad... never tried to delete top before, just assumed it should work, why is it that you cant peter? or better yet, how do you retrict rows in a delete query?

John
Commented:
So this is what I had to do to get it working for my table...
DELETE * FROM (SELECT TOP 25 PERCENT *  FROM (SELECT * FROM KashMtg ORDER BY StepDate));
Oddly (to me anyway) when I tried to simply use:

Select TOP 25 PERCENT * FROM KashMtg ORDER BY StepDate;

it returned all rows, imbedding in the convoluted way may be a kludge but it worked.

so...

SELECT * INTO tblCutMeUp FROM tblOriginal;

and then move them over 25% at a time...

SELECT TOP 25 PERCENT * INTO tblQuarter1 FROM (Select * FROM tblCutMeUp ORDER BY TotalField);
DELETE * FROM (Select TOP 25 PERCENT * FROM (Select * FROM tblCutMeUp ORDER BY TotalField));

SELECT TOP 25 PERCENT * INTO tblQuarter2 FROM (Select * FROM tblCutMeUp ORDER BY TotalField);
DELETE * FROM (Select TOP 25 PERCENT * FROM (Select * FROM tblCutMeUp ORDER BY TotalField));

SELECT TOP 25 PERCENT * INTO tblQuarter3 FROM (Select * FROM tblCutMeUp ORDER BY TotalField);
DELETE * FROM (Select TOP 25 PERCENT * FROM (Select * FROM tblCutMeUp ORDER BY TotalField));

And all remaining records go into tblQuarter4

SELECT * INTO tblQuarter4 FROM tblCutMeUp ORDER BY TotalField;
DELETE * FROM tblCUtMeUp;

Sorry for the confusion, thanks for pointing out the error of my ways peter, (and check me again please? :)

John
Commented:
If you are deleting from the source table then after you have deleted the first 25% you have changed the source.
The 2nd 25% of the original is not the same as the top 25% of the reduced/remaining data. It will be more or less the top 33.3% of the remaining data.
Then the 3rd 25% of the original is 50% of the data which remains after the first two bites.
The final chunk is 100% of the remainder.

Pete
Commented:
yikers, being dense this morning! I thought beer and pizza was a good thing?
of course that wont work... should have seen it even in the fog of the morning after. I'll just shut up and watch now :)

The Author didnt ask for a strictly SQL solution and it seems it would need to be a bit convoluted considering how restrictive TOP is. I'll stand by a VBA solution to this one.

John
Author Commented:
This is great guys -- many thanks!  One question though -- I ran into a "Characters found after SQL statement" error.  I'm running Access 2000 -- does this accept multiple sql statements in a single query?

Thanks!

Farron
Commented:
nope, no such luck.

querys have to be run in succession or you can use a vba module and use DoCmd.execute as
long as they are action queries... otherwise you use Docmd.RunSQL

JOhn

Commented:
if you are using an sql solution that resemble that mess i dropped up there make sure that you change the TOP <value> PERCENT so that for the first it's 25, then 33.3 then 50 - after the thrid you can just SELECT * INTO. you counts will probably not be exact quarters. i saw a variance of 22 records between qtr1 and qtr2 with a table containing 8894 records.

if exact is important see the vba solution further up.

John

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.

Author Commented:
Great -- thanks both of you.
###### 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.