Top percents in category by query?

[Lobb[cz]]

Hi everybody,

First of all please excuse my English. I have problem I cannot solve.
Have Access table with several columns - Item #, Category #, "Percast"
where "Percast" is share of all sales in Category. Here is an example:


Item Category Percast
1 100 26
2 100 18
3 100 7
4 100 3
5 200 11
6 200 6
7 200 5
8 200 2
9 300 16
10 300 12
11 300 7
12 300 2

Item Nr. 1 have 26 % share in Category 100.
...
Item Nr. 12 have 2% share in Category 300.

I need to select items, that have (together) at least 20 % share in
Category. And I need to find a way how to select these items.

In category 100 it should select only item Nr. 1 (because this item
have share over 20 %).

In category 200 it should select items Nr. 5, 6, 7 (Percast 11+6+5 >
20).

In category 300 it should select items Nr. 9 and 10 (Percast 16+12 >
20).

Not easy, I know Hope you understand what I need and hope that
anybody in here will help me to find the right solution.

Thanx.

 

[Guest]

You should find the following query will do it, where Sales is the name of
the table.

SELECT *
FROM Sales AS S1
WHERE NOT EXISTS
(SELECT *
FROM Sales AS S2
WHERE S2.Category = S1.Category
AND S2.Percast = S1.Percast
AND (SELECT SUM(Percast)
FROM Sales AS S3
WHERE S3.Category = S2.Category
AND S3.Percast > S2.Percast) >= 20);

The way it works is:

1. The first subquery identifies rows where the Category and Percast values
are the same as the outer query's current row, and where the second subquery
returns a value of 20 or more.

2. The second subquery sums the Percast values where the Category value is
the same as that in the second subquery's current row and where its Percast
value is greater.

3. The outer query returns only those rows where the first subquery returns
no rows.

One thing to be aware of is that if two items in the same category have the
same Percast value, and another item in the same category has a higher value,
then both items with the duplicate value will be returned by the query even
if the total is greater than 20 with only one of these duplicated items taken
into account. For example with:

5 200 13
6 200 8
7 200 8
8 200 2

Items 5, 6 and 7 would be returned. This type of result is generally
regarded as correct in this sort of situation. Its similar to when two
runners come in second in a race with exactly the same time; it would
obviously be wrong to put one before the other as they have tied for second
place.

The same would be true if two or more rows both have identical values of 20
or more, where each would be returned, e.g. with:

5 200 21
6 200 21
7 200 8
8 200 2

Items 5 and 6 would both be returned.

BTW you have no need whatsoever to excuse your English, which is excellent.
Your explanation was perfectly clear, far more so than many messages posted
here by people whose first language is English!

Ken Sheridan
Stafford, England