Other Links
This is one of the 33 lessons from the
Tutorial on Excel
Lesson 1-5: Microsoft Query in Excel and Web Queries
Data can be extracted from 99% of the databases. The same is possible from specialized programs (financial, accounting, manufacturing, etc.) because they store the data in a database. These extracts can be made available in Excel for the analysts.
NOTE: You will need to use your Microsoft Office CD to install Microsoft Query before using it.
Database Queries: Data Imported from an Access Database (First Level)
If you want to import a complete table or query from an Access database use the "Data Import" approach shown in lesson 1-4.
Data Imported from other Databases or Queries in Access Using Filtering and Sorting(Second Level)
If you want to import part of a table or join tables and queries in Access or if you want to import data from another type database you will go to the menu and use "Data/Import External Data/New Database Query".
The first dialog window that is shown to you will look like this one:
You are now in Microsoft Query. The list in your window will be different from this one but the first item is "New Data Source". The other elements are links that have been created earlier and that you can use in other workbooks.
If you click on the tab "Queries" you will see the list of all the queries that you have already developed and that reside on your computer at "Application Data/Microsoft/Queries".
So in the tab "Databases" you select "New Data Source" and you click on "OK". A second window is shown to you:
In the first text box you enter a name for the query you are about to create. This name will appear in the first dialog window (above) when you open it the next time.
Once you have created the name the second text box is enabled and if you click on the small arrow you will see a list of drivers (what type of database you will be querying).
In this lesson we will use the driver for Access. When you click on "Connect" this fourth dialog window appears:
You click on "Select" and a fifth dialog window appears:
In this window you first select the drive in "Drives" and you look for the database in the list of directories "Directories". When the database name appears in the list on the left you select it and you click on "OK".
The second dialog window comes back and you can choose a table or query in the last text box. When you do so a sixth dialog window appears:
In this window you decide what fields (columns) that you want to import. You do so by double clicking on them in the left list or by using the small arrows between the two lists. Once you have selected the fields you click on next and another window appears:
On the left there is the list of fields and on the right a set of 6 drop-down lists that will allow you to apply filters to import just part of the data. When you click on the arrow of the first DDList you see a list of types of filters and when you choose one the second DDList show you the list of values from the field. You can define 3 filters using "and" and "or" scenarios.
Once the operation is done you click on "Next" and yet another window appears:
In this window you can sort the data using three fields. You can sort "Ascending" or "Descending" for each of them. Then you click on "Next" and another dialog window appears:
At the end of the process the query will be saved within the workbook but if you are planning to use the same query I another workbook or install the same query or another computer later you might want to save it by clicking on the "Save Query..." button. If you want to edit the query and see the data before importing it select "View data or edit.." and click "OK" you will then see the query development interface shown in the section below. If you are ready to import just click on "Finish" for the final dialog window:
In this window you select where you want the query to send the data.
To automate the query (Automatic Refresh) see the section "Automating the query" in lesson 1-4
Data Imported from other Databases or Complex Queries in Access (Third Level)
If you want to edit an existing query including adding tables and queries from the database to create joints and develop a very complex query you need to get to the Microsoft Query interface.
To do so there are three ways in:
- when you click "Data/Import External Data/New Database Query". You click on "Cancel" in the "Query Wizard-Choose Columns" (above) develop your query, in the final dialog window "Query Wizard Finish" (above) select "View data or edit Query in Microsoft Query"
- right click anywhere within the imported data in Excel, select "Edit Query and when you get to the dialog window "Query Wizard-Filter Data" (above) click on "Cancel" and answer "Yes".
A new window opens and it looks like this:
In this window you can develop more sophisticated queries adding tables and queries from the source and joining them, sorting the data or filtering it.
Data imported from the WWW
Go to "Data/Import External Data/New Web Query" . The following window appears. In "Address:" enter http://www.excel-examples.com/00-excel-web-data.htm to see exactly what is shown in the image below.
Now you would normally click on the small arrow left of the table (Date,Product...) to import it. But there is a bug. If you want to import the table you must click on the yellow arrow left of "Data" . Do it and click "Import" the data is in your workbook.
Once the process has been set you simply need to select any cell of your data set and to go to "Data/Refresh Data" to refresh the data automatically.
You can modify the query by going to "Data/Import External Data/Modify Query"
This is one of the 33 lessons from the
Tutorial on Excel
