Top 5

[Guest]

Hi,

I have a number of projects in a database with an total score assigned to
them.

I'd like to create a subreport that has the top 5 and bottom 5 projects
based on the total score.

How do I specify in a query that I only want to display the top/bottom 5
total scores.

Cheers,

Phil

 

[JohnFol]

Save the following query and base the report on it.

SELECT TOP 5 Projects.*
FROM Projects
ORDER BY Projects.TotalScore;
union all

SELECT TOP 5 Projects.*
FROM Projects
ORDER BY Projects.TotalScore desc;

If you want the report ordered in a certan way, you'll have to do a 2nd
query, ie

Select * from MyUnionQueryAsAbove order by TotalScore

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

John & Phil,

Close, but that doesn't work. Run each SELECT query separately, then
run them as a UNION query. Compare the results of the separate runs to
the UNION run. The values will be different.

This, more complicated query, is what you want (Access 2002 w/ ANSI SQL
92 option checked - tested OK):

SELECT ProjectID, Total_Score
FROM
(SELECT ProjectID, Total_Score
FROM
(SELECT TOP 5 ProjectID, Total_Score
FROM Projects
ORDER BY Total_Score)

UNION ALL

(SELECT TOP 5 ProjectID, Total_Score
FROM Projects
ORDER BY Total_Score DESC)

) AS A
ORDER BY Total_Score DESC

If you aren't using the ANSI SQL-92 option you'll have to put each
SELECT TOP 5... query in a separate QueryDef and use this query:

SELECT ProjectID, Total_Score
FROM
[SELECT ProjectID, Total_Score
FROM qryTop5
UNION ALL
qryBottom5
]. AS A
ORDER BY Total_Score DESC

Note the use of the square brackets [] to delimit the derived table (aka
tabular subquery). Note the period immediately after the right bracket:
it is required. No square brackets are allowed inside the derived
table. Sometimes, Access chokes when these types of queries are viewed
in Design view so DO NOT view the query in design view.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQi9qBIechKqOuFEgEQIpnACfVyWE//D8Ijr3yyxXlTltEtIMgt0AoN4l
+wxsb/8gA1O60DF9BNVGOryn
=FbZl
-----END PGP SIGNATURE-----