SELECT does not return numeric value

Question

I have a table 'books'. For simplicity, here are 3 columns:

name(text) | new(boolean) | press(text) 

I need to select publishing house(press), which has most new books. I don't understand why Access shows "Syntax error".

SELECT press, COUNT(new) AS [Number of new books]
FROM books
WHERE new = TRUE
GROUP BY press
HAVING COUNT(new) = 
  SELECT MAX(s) 
  FROM 
  (   SELECT COUNT(new) AS s, press 
      FROM books
      WHERE new = TRUE
      GROUP BY press
  )

If I write only the second part:

  SELECT MAX(s) 
  FROM 
  (   SELECT COUNT(new) AS s, press 
      FROM books
      WHERE new = TRUE
      GROUP BY press
  )

It returns correct value (5)

If I write only 1st part with condition = 5, it's good.

SELECT press, COUNT(new) AS [Number of new books]
FROM books
WHERE new = TRUE
GROUP BY press
HAVING COUNT(new) = 5

What am I doing wrong?

Answer 1

The second query should be enclosed with parenthesis

SELECT press, COUNT(new) AS [Number of new books]
FROM books
WHERE new = TRUE
GROUP BY press
HAVING COUNT(new) = 
(
  SELECT MAX(s) 
  FROM 
  (   SELECT COUNT(new) AS s, press 
      FROM books
      WHERE new = TRUE
      GROUP BY press
  )
)

Answer 2

You already have an aggregate (GROUP BY) query which returns the count of new books per press. Use that as a subquery and take the TOP 1 row after ordering by the count in descending order.

SELECT TOP 1
    sub.press,
    sub.CountOfNew AS [Number of new books]
FROM
    (
        SELECT
            press,
            COUNT(*) AS CountOfNew
        FROM books
        WHERE [new] = TRUE
        GROUP BY press
    ) AS sub
ORDER BY sub.CountOfNew DESC

Note, if multiple presses are tied for the maximum [Number of new books], this query will return all the ties. (But then so will your original approach.) If you only want one of those ties, include a unique field (such as the primary key) in the ORDER BY.