Solved

Dividing data into quartiles

Posted on 2003-12-07
10
485 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

726 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