MS-Access - you tried to execute a query that does

Question

SELECT SUM(orders.quantity) AS num, fName, surname
FROM author
INNER JOIN book ON author.aID = book.authorID;

I keep getting the error message: "you tried to execute a query that does not include the specified expression "fName" as part of an aggregate function. What do I do?

Answer 1

The error is because fName is included in the SELECT list, but is not included in a GROUP BY clause and is not part of an aggregate function (Count(), Min(), Max(), Sum(), etc.)

You can fix that problem by including fName in a GROUP BY. But then you will face the same issue with surname. So put both in the GROUP BY:

SELECT
    fName,
    surname,
    Count(*) AS num_rows
FROM
    author
    INNER JOIN book
    ON author.aID = book.authorID;
GROUP BY
    fName,
    surname

Note I used Count(*) where you wanted SUM(orders.quantity). However, orders isn't included in the FROM section of your query, so you must include it before you can Sum() one of its fields.

If you have Access available, build the query in the query designer. It can help you understand what features are possible and apply the correct Access SQL syntax.

Answer 2

I had a similar problem in a MS-Access query, and I solved it by changing my equivalent fName to an "Expression" (as opposed to "Group By" or "Sum"). So long as all of my fields were "Expression", the Access query builder did not require any Group By clause at the end.

Answer 3

GROUP BY can be selected from Total row in query design view in MS Access.
If Total row not shown in design view (as in my case). You can go to SQL View and add GROUP By fname etc. Then Total row will automatically show in design view.
You have to select as Expression in this row for calculated fields.