Introduction to Structured Query Language - Database Processing: Fundamentals, Design, and Implementation - page 72

Notice that one of the rows of the Climbing department has been removed from the count
because it did not meet the WHERE clause condition. Without the ORDER BY clause, the rows
would be presented in arbitrary order of Department. With it, the order is as shown. In general,
to be safe, always place the WHERE clause before the GROUP BY clause. Some DBMS products
do not require that placement, but others do.

Does Not Work with
Microsoft access
aNSI-89 SQL

Microsoft Access does not properly recognize
the alias Dept_SKU_Count in the ORDER BY
clause and creates a parameter query that
requests an input value of as yet nonexistent
Dept_SKU_Count! However, it doesn't matter whether you enter parameter values or
not—click the OK button and the query will run. The results will be basically correct,
but they will not be sorted correctly.

Solution: Use the Microsoft Access QBE GUI to modify the query structure. The correct
QBE structure is shown in Figure 2-22. The resulting Microsoft Access ANSI-89 SQL is:

/* *** SQL-Query-CH02-43-Access-A *** */
SELECT SKU_DATA.Department, Count(*) AS Dept_SKU_Count

FROM SKU_DATA

WHERE (((SKU_DATA.SKU)<>302000))

GROUP BY SKU_DATA.Department

ORDER BY Count(*);

which can be edited down to:

/* *** SQL-Query-CH02-43-Access-B *** */
SELECT Department, Count(*) AS Dept_SKU_Count

FROM SKU_DATA

WHERE SKU<>302000

GROUP BY Department

ORDER BY Count(*);

Edit the query in the
QBE GUI interface so
that it appears as
shown here

Figure 2-22
Editing the SQL Query in the
Microsoft Access 2013 QBE
GUI Interface