Basic SQL Syntax for Access Queries

[MMcCarthy]

To view Access queries in SQL rather than Access query design - open the query design window and change the view to SQL:

Select Statement
SELECT [column_name] FROM [table_name];

Append Statement
INSERT INTO [table_name] ([column1], [column2], [column3])
VALUES ('value1', #value2#, value3);

This assumes value1 is a string, value2 is a date and value 3 is some other datatype

Update Statement
UPDATE [table_name] SET [column_name] = 'value1'
WHERE [other_column]=value2;

Delete Statement
DELETE * FROM [table_name];

Create Table Statement
SELECT Column1, Column2 INTO NewTable
FROM OldTable;

Distinct values only
SELECT DISTINCT [column_name] FROM [table_name];

Top 10 in an Ordered Query
SELECT TOP 10 [column_1], [column_2]
FROM [table_name]
ORDER BY [column_1];

Order by is Ascending by default. Use DESC at the of the statement to reverse the order.

IN Value List
SELECT [column_name]
FROM [table_name]
WHERE [column_name] IN ('value1', 'value2', 'value3');

Between Numbers
SELECT [column_name]
FROM [table_name]
WHERE [column_name] BETWEEN value1 AND value2;

Between Dates
SELECT [column_name]
FROM [table_name]
WHERE [column_name] BETWEEN #value1# AND #value2#;

Like and * wildcard
SELECT [column_name]
FROM [table_name]
WHERE [column_name] LIKE '*value*';

Count
SELECT COUNT([column_name])
FROM [table_name];

However in Aggregate queries if other columns are returned you must use Group By

SELECT COUNT([column_1]), [Column2]
FROM [table_name]
GROUP BY [Column2];

JOINS
SELECT [Table1].[Column_1], [Table2].[Column_2]
FROM Table1 INNER JOIN Table2
ON [Table1].[ID] = [Table2].[ID];

LEFT, RIGHT and INNER JOINS follow the same syntax.

Full outer joins are achieved by using no join as follows:

SELECT [Table1].[Column_1], [Table2].[Column_2]
FROM Table1, Table2;

Union
SELECT [column_name] FROM [table_1]
UNION [ALL]
SELECT [column_name] FROM [table_2];

The ALL predicate is required if you don't want duplicate records to be dropped.
UNION on it's own has the effect of using the DISTINCT predicate in a SELECT clause.

[abolos]

good job mmaccarthy. very useful SQL statements.

[Lysander]

Nice set of SQL statements.

Would it be worth adding the data manipulation SQL, ie DROP TABLE, CREATE TABLE etc

[MMcCarthy]

Nice set of SQL statements.

Would it be worth adding the data manipulation SQL, ie DROP TABLE, CREATE TABLE etc

I think a separate thead of more advanced SQL statements would be appropriate. Could also include crosstabs.

[sierra7]

A very good summary. I keep Acc97 loaded because the Help there is much better than in Acc2000+

One added tip for the Append Statement;-

INSERT INTO [table_name] ([column1], [column2], [column3])
VALUES ('value1', #value2#, value3);

is to use single quotes around decimal numbers if the software is to be used in continental Europe (not UK) because they use a comma instead of a full-stop (period) as the decimal symbol. Thus 12.5 would be displayed as 12,5 in the French locale and so interpreted as two values by Access, which errors moaning about number of values not matching.

If writing for europe it is necessary to put a Format statement on dates "yyyy-mm-dd" to ensure they are stored correctly.