I’m no stranger to programming, but I’m not very good with SQL or VBA and am looking for a starting point for the following scenario:
I have a simple database like so:
(number of players per team and games are variable and are imported from another source)
What I would like to do is to generate a report showing who scored the highest and what team they were from using GAME as the index. Example output below:
I don’t know if there is a way in SQL to generate the output above or if Access has some built in functions to achieve this (or if the table structure in general needs to be redesigned entirely…)
So far, here are the queries I’ve come up with:
To get the list of games: SELECT DISTINCT(GAME) from table;
To get the highers scorer for game A: SELECT TEAM, PERSON, SCORE FROM table WHERE GAME=’A’;
I want to avoid creating a large union query for each possible game there is because they are subject to change. So I’d like to iterate through the results of the distinct query to use in the WHERE statement of my second query (unless there’s a better way to go about this…I am completely new to all of this).
Thank you in advance!
