Access SQL SELECT, SELECT TOP, SELECT INTO

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_tbl
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.

access_SQL_select

Switch to SQL View and see how our input is displayed

SELECT Employee_tbl.[Employee ID], Employee_tbl.[Employee Name]
FROM Employee_tbl;
Query Result
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_tbl
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"));
Query Result
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

Peter_tbl
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

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *