How do you Create a query in Access?

Let us understand how to create queries in this chapter.

Create an Update Query

You can use an Update Query to change the data in your tables, and you can use an update query to enter criteria to specify which rows should be updated. An update query provides you an opportunity to review the updated data before you perform the update. Let us go to the Create tab again and click Query Design.

In the Tables tab, on the Show Table dialog box, double-click on the tblEmployees table and then close the dialog box.

On the Design tab, in the Query Type group, click Update and double-click on the field in which you want to update the value. Let us say we want to update the FirstName of “Rex” to “Max”.

In the Update row of the Design grid, enter the updated value and in Criteria row add the original value which you want to be updated and run the query. This will display the confirmation message.

Click Yes and go to Datasheet View and you will see the first record — FirstName is updated to “Max” now.

Create a Delete Query

You can use a delete query to delete data from your tables, and you can use a delete query to enter criteria to specify which rows should be deleted. A Delete Query provides you an opportunity to review the rows that will be deleted before you perform the deletion. Let us go to the Create tab again and click Query Design.

In the Tables tab on the Show Table dialog box, double-click the tblEmployees table and then close the dialog box.

On the Design tab, in the Query Type group, click Delete and double-click on the EmployeeID.

In the Criteria row of the Design Grid, type 11. Here we want to delete an employee whose EmployeeID is 11.

Let us now run the query. This query will display the confirmation message.

Click Yes and go to your Datasheet View and you will see that the specified employee record is deleted now.

Create a Make Table Query

You can use a make-table query to create a new table from data that is stored in other tables. Let us go to the Create tab again and click Query Design.

In the Tables tab, on the Show Table dialog box, double-click the tblEmployees table and then close the dialog box.

Select all those fields which you want to copy to another table.

In the Query Type, select the Make Table option button.

You will see the following dialog box. Enter the name of the new table you want to create and click OK.

Now run your query.

You will now see the following message.

Click Yes and you will see a new table created in the navigation pane.

Create a Make Table Query

1. Click the Create tab on the ribbon.
2. Click the Query Design button.
   

   The Query Design window and Show Table dialog box both appear. Here you need to select the tables and/or queries containing the data you want to include in your new table.

3. Double-click the tables and queries you want to add and click Close.

   Now add the fields you want to use in your new table.

4. Select the fields that you want to include and click Close.
5. Add any limiting criteria.

   Now you need to change the query type to make it a Make Table query.

6. Click the Make Table button on the ribbon.

   Access displays the Make Table dialog box. Here you need to tell Access the name of the new table.

7. Enter a name for your table and click OK.

   In the Table Name box, you can also choose to replace an existing table instead of creating a new one. To do this, click the Table Name list arrow and select a table. If the table you're replacing is in another database, first select the Another Database option and browse to the database.

Preview and Run a Query

Now you're ready to have the Make Table query create the new table. Preview the results of the query first.

1. Click View to preview the query in Datasheet View.

   The Make Table query displays the records it will use to create the new table.

2. Click View to return to Design View.

   Now you can run the query to create the new table.

3. Click the Run button on the ribbon.

   Access asks you to confirm the creation of the table.

4. Click Yes.

Access creates the new table based on the results of the Make Table query.

How do you create a query step by step?

How many ways can you create a query in Access?

What is a simple query in Access?

How do you create a query code?