Top 5 .... Bottom 5?

[Guest]

Hi,

Recently I posted a message asking if there was any way to select the Top 5
records based on a Total Score field.

I was told to use the TOP function in the query...which worked great!

But is there a opposite, Bottom function... that returns the records with
the lowest 5 scores, i tried the obvious keywords to to no avail.

Any help would be great

Phil

 

[Lynn Trapp]

Use a Descending Sort on your Top 5 query.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[Guest]

Cheers Lynn,

But dosn't that just give you the top five records in descending order. I
want the 5 records with the lowest total score, or the lowest 5.

Phil

 

[Rick Brandt]

Cheers Lynn,

But dosn't that just give you the top five records in descending
order. I want the 5 records with the lowest total score, or the
lowest 5.

No. In this case "TOP" means "the records at the top of the result set".
It does not mean the same as MAX or HIGHEST.

 

[Lynn Trapp]

Hi Phil,
No, It sorts the full table in descending order first and then give you the
top 5 records in that result set. Try it and see.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[Guest]

Your both right!

Cheers for the help.

Phil

Hi Phil,
No, It sorts the full table in descending order first and then give you the
top 5 records in that result set. Try it and see.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[Lynn Trapp]

You're quite welcome.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[MGFoster]

Hi,

Recently I posted a message asking if there was any way to select the Top 5
records based on a Total Score field.

I was told to use the TOP function in the query...which worked great!

But is there a opposite, Bottom function... that returns the records with
the lowest 5 scores, i tried the obvious keywords to to no avail.

Any help would be great

Phil

Phil, I answered your original post with this:

-----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-----