Putting records into deciles from descending order 1

[vladk]

Hi,

I have an Access table with a field, containing decimals ranging from 0.98099443222 to 0.01446670302. I need to sort these records in descending order and then group all records (there about 214,000 of them) into 10 equal buckets (equal number of records in each).

I assume, I can add one more field to house the bucket's indicator, but i need help to actually populate it.

If anybody could give me advice or even show some SQL code, I would greatly appretiate it!

Thank you!

 

[randy700]

How about something like this? (NOT TESTED)

QueryOne

SELECT Top 10 NbrField FROM TableName DESC

QueryTwo

SELECT Top 10 NbrField FROM TableName DESC WHERE IDField NOT IN (SELECT Top 10 NbrField FROM TableName DESC)

QueryThree

SELECT Top 10 NbrField FROM TableName DESC WHERE IDField NOT IN (SELECT Top 20 NbrField FROM TableName DESC)

etc...


Randy

 

[vladk]

randy700,

Thank you! I got an idea.

)

 

[dhookom]

You might also want to try the following SQL with a couple subqueries. You will want to make sure the number field is indexed.

SELECT tblRandomNumbers.NumberField, 
Int((SELECT COUNT(*) FROM tblRandomNumbers N WHERE N.NumberField<tblRandomNumbers.NumberField)/(SELECT COUNT(*) FROM tblRandomNumbers)*10)+1 AS Bucket
FROM tblRandomNumbers
ORDER BY tblRandomNumbers.NumberField;

Duane
Hook'D on Access
MS Access MVP