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