This Access tutorial explains how to use SQL SELECT, SELECT TOP and SELECT INTO statement.
Access SQL Select Statement
SELECT is the most fundamental syntax in SQL. SELECT Statement defines what fields we want to retrieve in the Query.
In this tutorial, I will demonstrate how to use SELECT, SELECT TOP and SELECT INTO statement.
Access SQL SELECT
Syntax of Select and Where
SELECT expressions FROM tables WHERE criteria
Select specific fields
Assume that we have created the below table.
| Employee ID | Employee Name | Date of Join |
|---|---|---|
| 1 | John | 1/1/2013 |
| 2 | Mary | 2/3/2014 |
| 3 | Peter | 1/5/2015 |
Create a Query in Design View and add table [Employee_tbl]. Select two fields as below.
Switch to SQL View and see how our input is displayed
SELECT Employee_tbl.[Employee ID], Employee_tbl.[Employee Name] FROM Employee_tbl;
| Employee ID | Employee Name |
|---|---|
| 1 | John |
| 2 | Mary |
| 3 | Peter |
As we can see, keyword SELECT and FROM are used to select specific fields from a table.
There are several points to note regarding the SQL syntax:
1) auto generated SQL have the the table name followed by each field name. For example,
Employee_tbl.[Employee ID]
Unless you have more than one tables using the same field name, you can omit the table name as below.
SELECT [Employee ID], [Employee Name] FROM Employee_tbl;
2) The square brackets of Field Name are used to group the two words together. When you create a field name or table name, it is a good practice not to leave a space if there are two words.
For example, you should use Employee_ID instead of Employee ID. In case you use two words, you should use square brackets in SQL.
3) The semi-colon ; at the end of the SQL is optional
Select all fields
You can use * to represent all fields. For example, the below SQL will get all fields from Employee_tbl.
Select * From Emplyee_tbl;
Access SQL SELECT TOP
It is possible that the SQL result returns more than two records for the same criteria.
In the below table, when we try to select [Employee Name]=Peter, records of Employee ID 3 and 4 will be returned.
| Employee ID | Employee Name | Date of Join |
|---|---|---|
| 1 | John | 1/1/2013 |
| 2 | Mary | 2/3/2014 |
| 3 | Peter | 1/5/2015 |
| 4 | Peter | 7/5/2015 |
Select Top N records
In order to select only 1 result, use SELECT TOP 1 to select only the first record. The first record means the first record of the Table that meets the criteria.
Note that you have to type the command in SQL View directly, not Design View.
The below SQL returns the record of Employee 3.
SELECT Top 1 *
FROM Employee_tbl
WHERE (((Employee_tbl.[Employee Name])="Peter"));
| Employee ID | Employee Name | Date of Join |
|---|---|---|
| 3 | Peter | 1/5/2015 |
You may also select the top Nth record. For example, Top 2 would return both Employee ID 3 and 4.
Select Top N percent records
You may also select top N percent records. For example, top 25% record means you want to return the top 1 record out of 4 records.
SELECT Top 25 PERCENT * FROM Employee_tbl WHERE (((Employee_tbl.[Employee Name])="Peter"));
Access SQL SELECT INTO
You can select data and then directly create a new table to store the result.
SELECT expressions INTO new_table FROM tables WHERE conditions;
Note that you have to type the command in SQL View directly, not Design View.
For example, we want to create a table called Peter_tbl for [Employee Name]=”Peter”
SELECT * INTO Peter_tbl FROM Employee_tbl WHERE [Employee Name]="Peter"
The below table will be created
| Employee ID | Employee Name | Date of Join |
|---|---|---|
| 3 | Peter | 1/5/2015 |
| 4 | Peter | 7/5/2015 |
| 5 | Peter | 8/5/2015 |
Outbound References
https://msdn.microsoft.com/en-us/library/bb208934%28v=office.12%29.aspx