Solved

Dividing data into quartiles

Posted on 2003-12-07
10
488 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
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 …

615 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