SQL Server and Access Query Syntax

The following table shows the corresponding differences between SQL Server and Access query syntax.

Access query syntax SQL Server query syntax
ORDER BY in queries ORDER BY in views not supported
DISTINCTROW DISTINCT
String concatenation with “&” String concatenation with “+”
Supported clauses/operators:

SELECT

SELECT TOP N
INTO
FROM
WHERE
GROUP BY
HAVING
UNION (ALL)
ORDER BY
WITH OWNERACCESS

Not Supported: COMPUTE, FOR BROWSE, OPTION

 Supported clauses/operators:

SELECT

SELECT TOP N
INTO
FROM
WHERE
GROUP BY
HAVING
UNION (ALL)
ORDER BY
COMPUTE
FOR BROWSE
OPTION

Not Supported: WITH OWNERACCESS
Aggregate functions:

AVG

COUNT(column)

COUNT(*)

MIN

MAX

FIRST

LAST

STDEV, STDEVP

SUM

VAR, VARP

 Aggregate functions:

AVG([ALL | DISTINCT] expression)

COUNT([ALL | DISTINCT] expression)

COUNT(*)

GROUPING (column_name)

MAX(expression)

MIN(expression)

STDEV, STDEVP

SUM([ALL | DISTINCT] expression)

VAR, VARP

Not supported: FIRST, LAST
TRANSFORM

     (SELECT statement)

 PIVOT

WITH ROLLUP, WITH CUBE on SELECT statements
MAKE TABLE, ALTER TABLE

Supported clauses:

CONSTRAINT

ADD COLUMN

DROP COLUMN

DROP INDEX

Also, stand-alone statement: DROP INDEX

 CREATE TABLE, ALTER TABLE

Supported clauses:

CONSTRAINT

ADD COLUMN

DROP COLUMN

Stand-alone statement: DROP INDEX