querying a percentage of table info

  • Thread starter Thread starter Cloudbuster
  • Start date Start date
C

Cloudbuster

If there are 3 unique groups listed in a table, is there a way I can make a
query that will show me the first 20% of records from each group, 2nd 20%,
etc.?
 
From Access HELP:

ALL, DISTINCT, DISTINCTROW, TOP Predicates
Specifies records selected with SQL queries.

Syntax
SELECT [ALL | DISTINCT | DISTINCTROW | [TOP n [PERCENT]]]
FROM table


-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
Cloudbuster said:
If there are 3 unique groups listed in a table, is there a way I can make
a
query that will show me the first 20% of records from each group, 2nd 20%,
etc.?
Click to expand...

maybe I don't understand the question or don't understand the answers.

I would get a count of the records in each group. I would generate a
sequence number for each item in each group, and do where between queries
using those sequence numbers to split into five groups.

and then, because I am cusious, I would then compare the speed of that
method with taking the top 20 percent as one query, run the second excluding
those in the first query.

Then the third excluding the results in the first two queries.

and so on to get the fourth and fifth groups.

all speculation, not tried.

Shouldn't we all be out partying?
 
David F Cox said:
maybe I don't understand the question or don't understand the answers.

I would get a count of the records in each group. I would generate a
sequence number for each item in each group, and do where between queries
using those sequence numbers to split into five groups.

and then, because I am cusious, I would then compare the speed of that
method with taking the top 20 percent as one query, run the second
excluding those in the first query.

Then the third excluding the results in the first two queries.

and so on to get the fourth and fifth groups.

all speculation, not tried.

Shouldn't we all be out partying?
Click to expand...

I should have added that if excluding the top 20% on the second query it
will be necessary to select top 25% because 1/5 are missing.

On the third query it will be top 33 1/3 % , fourth top 50%
 
Back
Top