Revision as of 17:05, 25 May 2012

Excel MS-Query

MS-Query is a utility that imports databases, text files and other spreadsheets into Excel.

This wiki uses the following data source:

This data is contained in several different formats.

Download these files here: xx to C:\Temp. Another directory can be used, but remember to translate C:\Temp to it.

Invoking MS-Query

The method used to invoke MS-Query varies by the version of Excel used. This wiki displays the user interface associated with Excel 2007. However, once in MS-Query, the utility looks the same regardless of version.

MS-Query is launched from the Data Ribbon

Depending on your Excel settings, the following message may appear:

Click on OK assuming you wish to continue. MS-Query’s Choose Data Source Window will open.

This window shows the various data connections available it includes:

• BFactory
• dBaseFiles
• Excel Files
• MS Access Database
• Visio Database Samples
• All ODBC Databases set up on the current computer
• All text data sources set up on the current computer

One of the nice things about MS-Query is that the end user does not need the application installed to access the data. For example, an MS-Query that is build against an MS-Access database can be used by anyone who has permissions to the directory in which the MS-Access database is located. The user will have to provide proper credentials if the database is password protected. The user will not need MS-Access installed on the local machine.

When MS-Query comes up for the first time, the Use the Query Wizard to create/edit queries button is checked. For beginning and intermediate level users the wizard works very well.

Exercise 1 – A Simple Query Against a Single Table

Perhaps 80%-90% of all data needed to be drawn from a database into Excel can be handled using the query wizard that comes with MS-Access.

This example uses the Table, tbl_employee in the sample MS-Access database, Employee.mdb. One of the advantages of MS-Query is that it does not care what version of MS-Access it is reading. Convert the sample database to whatever version works best for the system.

Launch MS-Query and select MS Access Database as the data source.

Navigate to the folder containing the database.

Note: MS-Query will make use of mapped drives if there are any. This causes issues when users map their drives differently.

It is recommended to type in the server name rather than using the navigation provided by the wizard. For example, type in \\Server Name\ Path in the Database Name box. Once a recognized path on the server is established, the wizard can be used to navigate from there.

In this case, almost everyone has a C drive so C:\Temp is good enough.

Click on Employee.mdb and click OK. This activity brings up a screen that looks like:

This exercise uses the table: tbl_employees. Any table or query can be used. Do not use a query that prompts for parameters. MS-Query will give an error message if such a query is used. There are ways to specify parameters at run time and they are explained in a later exercise.

Clicking on the table and right carrot (>) selects select all items. Specific items can also be selected in the order in which they are to be displayed.

For this example all items were selected. Click Next and the following screen appears.

This screen selects records based on specified criteria. Operators such as equals, greater than, greater than or equal to, not equal to, like, etc. can be used in conjunction with the field.

Note: if filtering is applied at this point, it becomes a “hard coded” part of the query. To change it, the query must be edited. There are ways of specifying queries at run time and they are discussed in a later exercise. Some criteria may have a hard-coded parameter of interest such as employee status = “Active.” Hard code only those criteria that are likely not to change.

Click Next

The next screen allows multiple level of sort.