Microsoft query

MS Query (Excel 2003) Tutorial

MS Query ist eine kostengünstige und einfache Alternative für SQL Datenauswertung, die den Vorteil hat, dass es ohne besondere Installation, universal verwendbar ist, da die meisten Mitarbeiter des Unternehmens Excel ohnehin verwenden. Die SQL Datenauswertung hat den Vorteil, dass Sie mehrfache Verknüpfungen auf mehreren Feldern ermöglicht, die beim SVERWEIS nurmit aufwendigen Schlüsseln möglich sind. Der SVERWEIS findet immer nur den ersten Treffer. Wenn es mehrere Treffer geben kann, dann werden diese vom SVERWEIS vorenthalten. SQL zeigt dagegen alle Treffer an. Verknüpfungen auf sehr lange Tabellen werden mit SQL viel schneller und „Arbeitsspeicher schonender“ ausgewertet als mit dem SVERWEIS. Im Gegensatz zu Pivot Tabellen, werden bei SQLAuswertungen sich wiederholende Zeilenüberschriften nicht ausblendet, die für späteres Weiterverarbeiten oder Filtern nützlich sind.


Um die Grundlagen von MS Query zu lernen, kann das folgende Beispiel “MS Query Tutorial, by Marty Ryerson, October, 2005“ benutzt werden.


The Sample Database
The mini-database that I created has three ranges organized as tables. Each of the tables has more rows thanare pictured below. I've included the images here to illustrate the data and how it's organized. The tables are:

1. A customer table named CUST:
[pic]

2. An order table named ORD:
[pic]

3. A sales rep table named SREP:
[pic]
Notice that like standard

Notice that like standard relational tables, these have certain fields in common.
Also notice how the second table is formatted.These formats are among the few that MS Query will recognize. To assign these formats, choose columns B and C and then assign the first format listed in Format, Cells, Number, Date. Then choose column F and assign the first format listed in Format, Cells, Number, Currency.
When using Excel as the source of data, it's important that each of the tables be a named range, because when MS Query usesworkbooks as a data source it will recognize only named ranges as tables. I usually place the ranges on separate sheets, but that isn't necessary.
Once you've set up these tables, save and close the workbook. The workbook must be closed when it is accessed by MS Query.



Create a Connection
Open another workbook where you will create your Excel report. Choose Data, Import External Data, NewDatabase Query, which launches the Choose Data Source dialog box.
(If MS Query isn't installed, a message will appear asking if you want to install it. To do so, place your installation disk in the appropriate drive and follow the on-screen instructions.)
[pic]
The first time you access a database, including a workbook database, you'll need to create a new Data Source. To do so, select the line,and then click OK.
In the first edit box of the Create New Data Source dialog, give your data source a name that will remind you what it is connected to. This is the name you will select from a list when you create new queries later.
[pic]
The item asks you to select the driver type. Because Excel is the source of data for this exercise, select the Excel driver shown from the drop-down list.Choose the Connect button and select the version of Excel you're working with. Notice that even if you use Excel 2003, the most-current version of Excel listed is Excel 97-2000.
[pic]
Choose the Select Workbook button, launching the Select Workbook dialog.
[pic]
Use this dialog to navigate to the workbook that will serve as your data source. Here, OEDATA.xls contains my Order Entry Data. Selectthe workbook from the list.
Choose OK to accept your Database Name selection. In the ODBC Microsoft Excel Setup dialog, choose OK to return to the Create New Data Source dialog. This dialog now shows the path to your Excel workbook that acts as your database.
[pic]
Choose OK to return to the Choose Data Source dialog. Note that the Data Source you just created is already selected in the...