14,534
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.
