Solved

Dividing data into quartiles

Posted on 2003-12-07
10
487 Views
Last Modified: 2011-10-03
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!!
0
Comment
Question by:flevy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
  • 2
10 Comments
 
LVL 10

Expert Comment

by:jobrienct
ID: 9894852
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
0
 
LVL 77

Expert Comment

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

Pete

0
 
LVL 10

Expert Comment

by:jobrienct
ID: 9895767
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Expert Comment

by:jobrienct
ID: 9895907
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...

to start with a copy:

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
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 100 total points
ID: 9896147
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
0
 
LVL 10

Expert Comment

by:jobrienct
ID: 9896210
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
0
 

Author Comment

by:flevy
ID: 9898254
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
0
 
LVL 10

Expert Comment

by:jobrienct
ID: 9898664
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

 
0
 
LVL 10

Accepted Solution

by:
jobrienct earned 400 total points
ID: 9898825
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
0
 

Author Comment

by:flevy
ID: 9898879
Great -- thanks both of you.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question