468,117 Members | 1,457 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

Basic SQL Syntax for Access Queries

MMcCarthy
14,534 Expert Mod 8TB
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
4 51489
abolos
65
good job mmaccarthy. very useful SQL statements.
Aug 11 '07 #2
Lysander
344 Expert 100+
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
14,534 Expert Mod 8TB
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
sierra7
446 Expert 256MB
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

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

10 posts views Thread by Marco Alting | last post: by
3 posts views Thread by Kevin Forbes | last post: by
3 posts views Thread by blackdevil1979 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.