Getting Data using MS Query in Microsoft Excel 2003
Importing Data from Access into a New Query
Data can be imported from an Access (or other) database into Excel via the
Import External Data command from the Data menu. You can also import
data stored in another Excel file. When importing data from Access use
New Database Query:
1. Start up Excel to get a new spreadsheet
2. From the Data menu choose Import External Data then New
Database Query...
3. On the Databases tab, select the Data Source type - MS Access
Database* - click on [OK]
4. Select the required file - change Drives to User (D:), open up the
Training folder and choose example.mdb then click on [OK] (the file
can be downloaded via the link provided here)
5. The Query Wizard now starts up:
i.
Select the Students table and click on the + to display the fields
- move across the first 6 fields up to and including Hall (double
click or select and click on [>]) - click on [Next>]
ii.
Set up a filter (none is required here) - click on [Next>]
iii.
Set up any sorting required (again none is required) - click on
[Next>]
iv.
Choose Return Data to Excel to simply paste in the data. View
Data or Edit Query in MS Query lets you create a parameter
query, which you will see later - click on [Finish]
6. Finally, state where you would like your imported data to appear -
which cell on which sheet, here choose A1 on the current empty sheet
- press <Enter> or click on [OK] to finish
Editing the Query
You can now make any amendments required by editing the query:
1. If it isn't already displayed, turn on the External Data toolbar: from the
View menu choose Toolbars and select External Data
2. Click on the [Edit Query] button - first one on the toolbar - to reactivate
the Wizard
3. Click on [Next>] (unless you wish to add extra columns)
4. Set up a filter - click on Hall in Column to filter then use the list arrows
to first select equals then choose any hall (eg Bridges) - click on
[Next>]
5. Set up a sort on Surna