Jun 02

Access Cheat Sheet

These are from ye olde Access 97 days and may no longer apply.

COUNT DISTINCT isn’t supported

To get around it: SELECT COUNT(1) FROM (SELECT DISTINCT column FROM table)

Joins

When using multiple ANSI joins, you must surround each set in parentheses.

SELECT topic.name
FROM (users INNER JOIN messages ON users.id = messages.idUser)
INNER JOIN topics ON messages.idTopic = topics.id

Object Names

If there are queer characters or reserved words in your table/query names, you can reference them in SQL statements by surrounding the name with square brackets, just like SQL Server.

SELECT email, [date] FROM [List Members]

Dates

Surround dates in SQL with #.

SELECT * FROM users WHERE joined > #3/4/2002#