How to Query to an Excel Spreadsheet

Querying Excel allows you to pull specific data from the spreadsheet. This may include text or values within the cells, such as an organization's name or zip code. You can link to a selected worksheet from Microsoft Access. When you link to the spreadsheet, the query is updated with the original file so that you don't have to perform a new query each time the spreadsheet changes. Additionally, you can modify the query at any time to retrieve other data.

Instructions

Linking to the Spreadsheet

1. Open a new or existing database in Microsoft Access.

2. Select "File," "Get External Data" and "Link Tables" from the toolbar.

3. Change "Files of type" to "Microsoft Excel (*.xls)" in the "Get External Data" dialog box.

4. Find and select the Excel file you need and click "Link."

5. Choose "Show Worksheets" in the "Link Spreadsheet Wizard" dialog box. Select the worksheet you want to query and click "Next."

6. Select "First Row Contains Column Headings" and click "Next."

7. Enter a linked table name and click "Finish." Access alerts you that the table is linked. Click "OK" to close the alert box.

Creating the Query

1. Select "Queries" from "Objects" in the Database window.

2. Double-click "Create query by using wizard" to open the "Simple Query Wizard."

3. Choose the Excel table you named (in step seven of the previous section) from the "Tables/Queries" drop-down list.

4. Double-click any Excel fields you want to query from "Available Fields" and click the right arrow to add them.

5. Click "Next" and type in a name for the query you're creating. Select "Modify the query design" and click "Finish." The fields in your query are displayed in the bottom pane in "Design" view.

6. Go to the "Criteria" section in the first column (field) you want to query. Type in the criterion you want the query to find. For example, to query the organization's name, type "XYZ Company" in quotes. To query all names starting with "Br," type "Br*" in quotes. To query a zip code, type "79100" without quotes. To query a date after June 1, 2008, type "<#6/1/2008#" without quotes. To exclude a specific zip code from your query, type "not 79100" without quotes.

7. Move to the next column and criterion you want to query from the spreadsheet. Repeat Step 6 to customize your query.

8. Click the "Save" button and then select "Query" and "Run" to view the results.

References

• Microsoft Office: Performing Microsoft Queries
• Font Stuff: Querying Access