SQL – Simple query with MS Access – Best In Class per Class

Posted on August 30, 2013 by

In this article with MS Access I will show how to create a good MS Access query, which results in best in class results. Let’s imagine, that we have the following data:

In this database, we have four columns – Country, City, TeamID and Score. The question is: “How should we select the teams with the highest scores per country?”. OK, with 14 teams we can do it manually, but I am aware that if you are reading this post, this is not what you have expected. So, we should try some basic SQL.

After spending much more time in the Internet, than I am willing to admit, I was able to construct the following SQL code:

SELECT LevelReport.city, Max(LevelReport.score) AS MaximumScore, LevelReport.country, LevelReport.teamID
FROM LevelReport
GROUP BY LevelReport.city, LevelReport.country, LevelReport.teamID
ORDER BY Max(LevelReport.score) DESC;

SELECT LevelReport.city, Max(LevelReport.score) AS MaximumScore, LevelReport.country, LevelReport.teamID

FROM LevelReport

GROUP BY LevelReport.city, LevelReport.country, LevelReport.teamID

ORDER BY Max(LevelReport.score) DESC;

Well, what it does is kind of acceptable – it shows the best team per country and sorts them in a descending order. In order to make it a little more complicated, we would request information only about the cities with more than one team. We just do the following in MS Access:

With the so generated query the result is beautiful:

We see the requested info for all the cities with more than one team. Thus Enschede and Warsaw are not present. The SQL for this query is the following:

SELECT LevelReport.city, Max(LevelReport.score) AS MaximumScore, Count(LevelReport.teamID) AS CountOfteamID
FROM LevelReport
GROUP BY LevelReport.city
HAVING (((Count(LevelReport.teamID))>=2))
ORDER BY Max(LevelReport.score) DESC;

SELECT LevelReport.city, Max(LevelReport.score) AS MaximumScore, Count(LevelReport.teamID) AS CountOfteamID

FROM LevelReport

GROUP BY LevelReport.city

HAVING (((Count(LevelReport.teamID))>=2))

ORDER BY Max(LevelReport.score) DESC;

We see, that the effect for <=2 is translated into SQL with the key word “HAVING”.

At last I have decided to show only the best Bulgarian and Dutch teams. For this I have generated a second query. The SQL code there is as follows:

SELECT LevelReport.city, Max(LevelReport.score) AS MaximumScore, LevelReport.country
FROM LevelReport
GROUP BY LevelReport.city, LevelReport.country
HAVING (((LevelReport.country)="Bulgaria" Or (LevelReport.country)="Netherlands"))
ORDER BY Max(LevelReport.score) DESC;

SELECT LevelReport.city, Max(LevelReport.score) AS MaximumScore, LevelReport.country

FROM LevelReport

GROUP BY LevelReport.city, LevelReport.country

HAVING (((LevelReport.country)="Bulgaria" Or (LevelReport.country)="Netherlands"))

ORDER BY Max(LevelReport.score) DESC;

You see, that the effect for filtering by countries is achieved again with the key words “HAVING” and “OR”. The countries are mentioned separately.

Thanks for reading the whole article. If you want to play by yourself with this two SQLs and you are not willing to spend 2 minutes on data entry for the creation of the database, you may use my file. Feel free to make any edition of the code 🙂

Here is the file!

Enjoy it!