How to Query Data From Excel in Access & Display Results in Excel
The Microsoft Excel spreadsheet program is widely used to perform calculations, make charts and store and sort data. While it is a powerful tool, many Excel users will benefit from the ability to use the spreadsheet data in different ways. Microsoft Access and Excel are designed to work together. The result is that you can easily manipulate data saved in an Excel spreadsheet using Access queries, relationships and reports. By following a few simple steps, you can access and refine data generated by either program.
Other People Are Reading
Instructions
-
-
1
Create the spreadsheet. Open Excel and click on "File" and "New." In row 1, add a header or column name to each column to facilitate later steps. Enter the data into the Excel spreadsheet.
-
2
Save the spreadsheet. Click "File" and "Save" in the menu bar. Name the file. Click the "Save" button. Click "File" and "Close" on the menu items or click the red X in the upper right corner of the Excel window.
-
-
3
Create the database. Open Access. Click "File," "New" and "Blank Database." Name the file. Click the "Create" button.
-
4
Link the Excel file. Click "File," "Get External Data" and "Link Tables." Change the Files of Type in the displayed Link window to Microsoft Excel (.xls). Select the Excel spreadsheet. Click the "Link" button.
-
5
Select the spreadsheet. Click the radial button to the left of "Show Worksheets" in the "Link Spreadsheet Wizard" window. Select the desired sheet in the area to the right of "Show Worksheets." Click "Next."
-
6
Select fields for table. Check the box next to "First Row Contains Column Headings." Click the "Next" button. The linked spreadsheet is displayed in the database "Tables" window.
-
7
Create the Query. Select "Query" in the "Objects" pane and click the "New" button. Select "Simple Query Wizard" from the displayed "New Query" window. Click the arrow buttons to add fields from the available fields to the query. Click the "Next" button. Name the query. Click the "Finish" button.
-
8
Export the query results to an Excel file. Click "File" and "Export." Change the "Save as Type" box to "Microsoft Excel." Click the "Export All" button.
-
9
In Excel, open the file you created in Step 8 by clicking "File" and "Open."
-
1
Tips & Warnings
The changes to data in the Excel spreadsheet are maintained when linking to the Excel spreadsheet in Access. When the target data is frequently modified, linking is preferable to importing tables.
Do not name the file created in Step 8 with the same name that you used in Step 1 or you will lose your data.
