Creating and Using Action Queries - Creating an Employee Addresses Table Using a Select Query
(Page 2 of 5 )
We can start with a Select query. After making the selections and applying conditions for filtering, if any, we can convert it into a make table query. The following design view of a Select query shows the six fields that are to be included in the table, named Address. We will use an expression to create a field which has the full name in one column using two of the fields from this query.
Here a table called Address will be created from the data in the Employees table as shown. We will be choosing the columns needed for the Address table. We will also add a column which has the First Name together with the Last Name, for which we need to build an expression. In an empty Field row in the above window, right click to bring up the context-sensitive menu. Choose Build... to bring up the Expression Builder as shown, expand the Tables node, highlight the First name, and click on Value in the highlighted area. This adds the code [Employees] ! [FirstName] to the expression as shown.
Now complete by adding a space between First Name and Last Name, and then add the Last name in a way that is similar to the way you added the First Name. Use the available buttons on the Expression builder as shown.
When you click on the OK button on the Expression Builder the concatenated string is added to your design view of the query as shown. The SQL view of the query is now as shown in the next paragraph. Save the query as EmployeeAddress.
SELECT Employees.EmployeeID, Employees.LastName, Employees.FirstName,
Employees.Address, Employees.City, Employees.PostalCode,
Employees.Country, [Employees]![FirstName] & " " &
[Employees]![LastName] AS Expr1
FROM Employees;
When you run this query by double-clicking the EmployeeAddress query icon you will see the following output. As you can see the last column shows the concatenated expression.
Go back to the design of the query and refine the query so that you do not choose the First Name and Last Name as shown in the SQL View (they are not included in the select list). In some versions of MS Access, when you open the query in SQL View, you may not be able to change it to the Query Design view.
SELECT Employees!FirstName & " " & Employees!LastName AS Emp_name,
Employees.Address, Employees.City, Employees.PostalCode,
Employees.Country
FROM Employees;
The column in blue is the 'Full Name' created by the Expression Builder. You replaced Expr1 with "Emp_name." The output of this query is now as shown in the next picture. You have rearranged everything just as you needed.
Next: Create an Address Table from the Select Query >>
More Microsoft Access Articles
More By Jayaram Krishnaswamy
