Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Dividing data into quartiles

Posted on 2003-12-07
10
Medium Priority
?
491 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 400 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 1600 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

730 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