- MS Access Tutorial
- MS Access Useful Resources
- Selected Reading
Click From Other Sources, From Microsoft Query. The 'Choose Data Source' dialog box appears. Select MS Access Database. and check 'Use the Query Wizard to create/edit queries'. Select the database and click OK. This Access database consists of multiple tables. You can select the table and columns you want to include in. In Excel, open the Data tab and choose From Other Sources - From Microsoft Query. Choose the Office365 DSN. Select the option to use Query Wizard to create/edit queries. In the Query Wizard, expand the node for the table you would like to import into your spreadsheet. Select the columns you want to import and click the arrow to add them to. Using Microsoft Query, you can join these tables so that all the customer records are retrieved, along with sales data for those customers who have made purchases. To start Microsoft Query, perform the following steps. On the Data tab, in the Get External Data group, click From Other Sources, and then click From Microsoft Query.
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.
Microsoft Query For Excel
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.
Microsoft Query 101
Microsoft Query Syntax
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.