ecojohnson
Programmer
Hello.
I have a problem that seems absolutely trivial, but I cannot solve it. I have a MS-Access database table (called REVIEWS) that has the following columns:
NAME NUMBER
Andrews 12
Green 4
Jones 8
Smith 15
Green 10
Jones 9
My goal is to write a query that returns the person with the highest number of reviews (in this example, it happens to be Jones). If you notice, he is in the list twice. I am trying to write a SQL query in Access that will acheive this result, but I am stumped. I have tried various combinations of using various functions (MAX, GROUP BY, DISTINCT), and I cannot come up with the result.
The basic query I keep coming back to is this:
SELECT NAME, SUM (NUMBER)
FROM REVIEWS
GROUP BY NAME
This query will return a list of the people and their total reviews. However, I want to return only the name of the reviewer with the most reviews (Jones). I keep trying to use a subquery within this query, but nothing seems to work. The only solution I can think of is to create a table based on a query, and then query the new table. However, I don't want to do this, and it sounds overly complicated for (what seems) a simple problem. I researched various SQL websites, and I cannot find an answer to my problem. I just cannot believe it is this difficult (which is why I am feeling so frustrated).
Does anyone know how to write a simple query to achieve the results I am looking for? If you need more info from me, please let me know.
Thanks!
I have a problem that seems absolutely trivial, but I cannot solve it. I have a MS-Access database table (called REVIEWS) that has the following columns:
NAME NUMBER
Andrews 12
Green 4
Jones 8
Smith 15
Green 10
Jones 9
My goal is to write a query that returns the person with the highest number of reviews (in this example, it happens to be Jones). If you notice, he is in the list twice. I am trying to write a SQL query in Access that will acheive this result, but I am stumped. I have tried various combinations of using various functions (MAX, GROUP BY, DISTINCT), and I cannot come up with the result.
The basic query I keep coming back to is this:
SELECT NAME, SUM (NUMBER)
FROM REVIEWS
GROUP BY NAME
This query will return a list of the people and their total reviews. However, I want to return only the name of the reviewer with the most reviews (Jones). I keep trying to use a subquery within this query, but nothing seems to work. The only solution I can think of is to create a table based on a query, and then query the new table. However, I don't want to do this, and it sounds overly complicated for (what seems) a simple problem. I researched various SQL websites, and I cannot find an answer to my problem. I just cannot believe it is this difficult (which is why I am feeling so frustrated).
Does anyone know how to write a simple query to achieve the results I am looking for? If you need more info from me, please let me know.
Thanks!