423,846 Members | 2,048 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Basic SQL Syntax for Access Queries

MMcCarthy
Expert Mod 10K+
P: 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.
Jan 12 '07 #1
Share this Article
Share on Google+
4 Comments


P: 65
good job mmaccarthy. very useful SQL statements.
Aug 11 '07 #2

Expert 100+
P: 344
Nice set of SQL statements.

Would it be worth adding the data manipulation SQL, ie DROP TABLE, CREATE TABLE etc
Aug 13 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Aug 13 '07 #4

Expert 100+
P: 446
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.
Sep 11 '07 #5