Full Version: Creating a top 3 list for each individual in a database
UtterAccess Forums > Microsoft® Access > Access Queries
discosammy
I'm having trouble with a query of individuals and test scores.
There are five different test types, which are thankfully numbered so a descending list will get me most of the way home.
But I only need/want the top three for each individual. I know TOP 3 needs to be in the eventual query, but when I add this, I get only get a handful of results THE top scores/levels.
I've got something like this:
SELECT TOP 3 table1.TECH_ID, table1.CAPP_LVL, Comment
FROM
(SELECT ST_LVL.TECH_ID, ST_LVL.TEST_LVL, Max(ST_LVL.TEST_LVL_SCORE) AS MaxScore
FROM ST_TEST_LVL
GROUP BY ST_LVL.TECH_ID, ST_LVL.TEST_LVL
HAVING ST_LVL.TEST_LVL="0005" Or ST_LVL.TEST_LVL="0007" Or ST_LVL.TEST_LVL="0008" Or ST_LVL.TEST_LVL="0009" Or ST_LVL.TEST_LVL="0011"
GROUP BY TECH_ID, TEST_LVL
Order BY table1.TECH_ID
ScottGem
Check out this article
balaji
This previous thread has the answer you want.
discosammy
Thanks. I did some forum search first, but this didn't come up in my results.
ppreciate the pointer.
discosammy
Thanks for the help. I'm a moron. I can solve this simply by writing better queries.
Since the Tests are numbered and it's either or for two of them, I was able to do it by joining two subtables.
Not that you'd know from the details i gave... just thought I'd post this in case this type of solution would work for whoever might stumble across this thread...
SELECT Top3Lvl.TEST_ID, Top3Lvl.MaxLvl, LvlScore.MaxScore
FROM
(SELECT TestLvls.TEST_ID, TestLvls.MaxLvl
FROM
(SELECT TEST_ID, Max(TEST_LVL) AS MaxLvl
FROM ST_TEST_LVL
WHERE TEST_LVL="0008" Or ST_TEST_LVL.TEST_LVL="0011"
GROUP BY TEST_ID
UNION ALL
SELECT TEST_ID, Max(TEST_LVL) AS MaxLvl
FROM ST_TEST_LVL
WHERE TEST_LVL="0007" Or ST_TEST_LVL.TEST_LVL="0006"
GROUP BY TEST_ID
UNION ALL
SELECT TEST_ID, Max(TEST_LVL) AS MaxLvl
FROM ST_TEST_LVL
WHERE TEST_LVL ="0003"
GROUP BY TEST_ID) AS TestLvls
Order BY TEST_ID, TEST_LVL) AS LvlScore
ON (Top3Lvl.MaxLvl=LvlScore.TEST_LVL) AND (Top3Lvl.TEST_ID=LvlScore.TEST_ID)
ORDER BY TEST_ID, Top3Lvl.MaxLvl
balaji
Thanks for posting your solution. thanks.gif
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.