MS Access 2003 - Top 20 Percent

[Chris]

The table name is: wasteexport

There are 5 fields: Site, Pid, Code, Des, Qty

Site is a number (can be text)

Pid is text

Code is a number

Des is text

Qty is a number

In the table the Code field is Ascending and the Qty field is Descending.

There could be several hundred of the same Code field i.e. Try to image there being 100x 001, 500x 002 etc.

001

001

001

002

002

003

etc

I've tried this in SQL:

SELECT TOP 20 PERCENT wasteexport.site, wasteexport.pid, wasteexport.code, wasteexport.des, wasteexport.qty
FROM wasteexport
ORDER BY wasteexport.code, wasteexport.qty DESC;

However this only returns the top 20 percent of data in the table, i.e 8036 records = returns 20% of that which is the first 1608 records.

I want to be able to select the top 20 percent of each Code data that it finds. As the Qty field is already descending it does not matter about this or does it - it needs to be descending.

For example if there are 500x 001 it should return the first 100. If there are 5x 001 it would only return the first one.

How do I specify the top 20 percent on the Code field?

[ɹəuəllıʍ ʇɐb]

I don't know that "TOP nn PERCENT" clause of SQL, but I can understand its meaning.

What I would try is a subquery on the Code column, then use this result to join the rest of the data.

I currently don't have MS Access available (that computer stands disconnected on the floor). But let me know if you need help coding the subquery.

P.S. is the Code column UNIQUE (i.e. no duplicate values)? If not, that may become difficult...

[ɹəuəllıʍ ʇɐb]

I just had a closer look at this

http://technet.microsoft.com/en-us/library/ms189463.aspx

It appears as if the TOP clause is related to the ORDER BY clause. Can you try it with only one column name (Code) in the ORDER BY ?

[Chris]

Yes there are duplicate Codes, but the other information in the record makes the record unique.

I'll try the order thing....and get back to you.

[Chris]

I didn't get the chance to do this in Access today but I have a feeling it wouldn't work as you are only ascending or descending the field by using ORDER BY.

The good news is I did it in Excel, but it involved a few formulas to get it done. However this is long winded.

I'll keep trying.