Top n Percent in subquery and EXISTS reserved keyword error

[Patti Schreiber]

Hi,
I am trying to write a query that returns the top 25 percent of multiple
fields. I have achieved the query I want by adding a rank coulmn in Excel,
but I want to skip this step. Here is the query that achieves the results
that I want.

SELECT [13L].[file name], [13L].[sum rank], [13L].[gage rank]
FROM 13L
WHERE ((([13L].[sum rank])<(SELECT Count([13L].[file name])/4 FROM 13L)) AND
(([13L].[gage rank])<(SELECT Count([13L].[file name])/4 FROM 13L)));

Without having added the sum rank and gage rank fields, I have acheived the
top 25 percent return for one field using the following:

SELECT TOP 25 PERCENT [13L].[file name],[13L].[sum], [13L].[gage],
FROM 13L
ORDER BY [13L].sum;

The problem is, I want to narrow down the results to return only those in
which the entry is in the top 25 percent of both the gage and the sum field.
I have attempted the following subquery, with the dreaded error 3306 (EXISTS
reserved keyword...) :

SELECT [13L].[file name], [13L].[sum rank], [13L].[gage rank]
FROM 13L
WHERE (([13L].[file name]) = (SELECT TOP 25 PERCENT [13L].[file
name],[13L].[sum] FROM 13L ORDER BY [13L].sum));

My question is how to achieve the results I want using subqueries, or is
there someway to easily create my rank field (calculate in a query an ordered
field) in Access?

 

[Brian]

Hi,
I am trying to write a query that returns the top 25 percent of multiple
fields. I have achieved the query I want by adding a rank coulmn in Excel,
but I want to skip this step. Here is the query that achieves the results
that I want.

SELECT [13L].[file name], [13L].[sum rank], [13L].[gage rank]
FROM 13L
WHERE ((([13L].[sum rank])<(SELECT Count([13L].[file name])/4 FROM 13L)) AND
(([13L].[gage rank])<(SELECT Count([13L].[file name])/4 FROM 13L)));

Without having added the sum rank and gage rank fields, I have acheived the
top 25 percent return for one field using the following:

SELECT TOP 25 PERCENT [13L].[file name],[13L].[sum], [13L].[gage],
FROM 13L
ORDER BY [13L].sum;

The problem is, I want to narrow down the results to return only those in
which the entry is in the top 25 percent of both the gage and the sum field.
I have attempted the following subquery, with the dreaded error 3306 (EXISTS
reserved keyword...) :

SELECT [13L].[file name], [13L].[sum rank], [13L].[gage rank]
FROM 13L
WHERE (([13L].[file name]) = (SELECT TOP 25 PERCENT [13L].[file
name],[13L].[sum] FROM 13L ORDER BY [13L].sum));

My question is how to achieve the results I want using subqueries, or is
there someway to easily create my rank field (calculate in a query an ordered
field) in Access?

1. Take your query which gives the top 25% by sum, and save it as, say,
qryTopQuartileSum.
2. Create a similar query which gives the top 25% by gage and save it as,
say, qryTopQuartileGage
3. Create the following query:

SELECT S.[file name], S.[sum], S.[gage] FROM qryTopQuartileSum S INNER
JOIN qryTopQuartileGage G ON S.[file name] = G.[file name]

 

[Jamie Collins]

Patti Schreiber ...

is
there someway to easily create my rank field (calculate in a query an ordered
field) in Access?

SELECT (
SELECT COUNT(T2.[file name])
FROM 13L T2
WHERE
T2.[file name]>=T1.[file name]
) AS rank,
T1.[file name],
T1.[sum rank],
T1.[gage rank]
FROM
13L T1
;

Jamie.

--

 

[Jamie Collins]

(e-mail address removed) (Jamie Collins) wrote ...

SELECT (
SELECT COUNT(T2.[file name])
FROM 13L T2
WHERE
T2.[file name]>=T1.[file name]
) AS rank,
T1.[file name],
T1.[sum rank],
T1.[gage rank]
FROM
13L T1
;

I meant to add, you can use the 'rank' and a subquery to determine the
top n% percent (is this obvious?) and avoid the MS proprietary TOP N
syntax which was giving you problems. I'd post some code but I'd have
to make assumptions about rounding, ties, duplicates, etc. Post some
more details (e.g. sample data and expected results) if you need
further help.

Jamie.

--