The Problem (Q-score 9, ranked #10th of 67 in the Access VBA archive)
The scenario as originally posted in 2013
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?
Why community consensus is tight on this one
Across 67 Access VBA entries in the archive, the accepted answer here holds elite answer (top 10 %%) status — meaning voters are unusually aligned on the right fix.
The Verified Solution — elite answer (top 10 %%) (+17)
12-line Access VBA pattern (copy-ready)
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.
When to Use It — classic (2013–2016)
A top-10 Access VBA pattern — why it still holds up
Ranks #10th of 67 in the Access VBA archive. The only pattern ranked immediately above it is “Insert record into table if entry does not exist in another table-…” — compare both if you’re choosing between approaches.
What changed between 2013 and 2026
The answer is 13 years old. The Access VBA object model has been stable across Office 2013, 2016, 2019, 2021, 365, and 2024/2026 LTSC, so the pattern still compiles. Changes that might affect you: 64-bit API declarations (use PtrSafe), blocked macros in downloaded files (Mark-of-the-Web), and the shift toward Office Scripts for web-first workflows.