Query question 1

[ecojohnson]

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!

 

[dinger2121]

This should do it -

SELECT TOP 1 Name, Sum(Number) AS SumOfNumber
FROM Reviews
GROUP BY Name;

 

[ecojohnson]

Hi dinger2121

Thanks for the tips. However, this does not work. Using the TOP function will return the first person, not the person with the highest number of reviews.

It seems I somehow need to use the MAX function, and I cannot do this without creating a new table from a query (similar to yours without the TOP function).

If you have other ideas, or if I am missing something, please let me know.

Thanks!

 

[dinger2121]

Try this....

SELECT TOP 1 Name, Sum(Number) AS SumOfNumber
FROM Reviews
GROUP BY Name
ORDER BY Sum(Number) DESC;

 

[nastar1]

Looks like a wrong turn at the first intersection got you on this. You need a second table with the Number of Reviews and a one to many relationship from the Names table back to it. then your Group By Sum of the Reviews in descending order would work great in your query.

 

[ecojohnson]

Yup - that was it. It was the ORDER BY that I was missing which did the trick.

Thanks so much!