Transcription
Show All
About using Microsoft Query to retrieve external dataThis topic contains information about:What is Microsoft Query?Setting up data sourcesDefining your queryWorking with the data in Microsoft Excel
What is Microsoft Query?Microsoft Query is a program for bringing data from external sources intoother Microsoft Office programs— in particular, Microsoft Excel. By usingQuery to retrieve data from your corporate databases and files, you don'thave to retype the data you want to analyze in Excel. You can alsoupdate your Excel reports and summaries automatically from the originalsource database whenever the database is updated with newinformation.Types of databases you can access You can retrieve data from severaltypes of databases, including Microsoft Access, Microsoft SQL Server,and Microsoft SQL Server OLAP Services. You can also retrieve datafrom Excel lists and from text files. See a list of types of databases youcan access.In Excel, you can also retrieve data from Web pages, but you don't needQuery to do this. For information about retrieving data from Web pages,see Excel Help.Selecting data from a database You retrieve data from a database bycreating a query, which is a question you ask about data stored in anexternal database. For example, if your data is stored in an Accessdatabase, you might want to know the sales figures for a specific productby region. You can retrieve a part of the data by selecting only the datafor the product and region you want to analyze and omitting the data youdon't need.
With Query, you can select the columns of data that you want and bringonly that data into Excel.Updating your worksheet in one operation After you have externaldata in an Excel workbook, whenever your database changes, you canrefresh the data to update your analysis— without having to recreate yoursummary reports and charts. For example, you can create a monthlysales summary and refresh it every month when the new sales figurescome in.Using Query to retrieve data Bringing external data into Excel withQuery takes three steps: First you set up a data source to connect toyour database, then you use the Query Wizard to select the data youwant, and finally you return the data to Excel where you can format it,summarize it, and create reports from it.Return to top
Setting up data sourcesWhat is a data source? A data source is a stored set of information thatallows Microsoft Excel and Microsoft Query to connect to an externaldatabase. When you use Query to set up a data source, you give thedata source a name, and then supply the name and location of thedatabase or server, the type of database, and your logon and passwordinformation. The information also includes the name of an ODBC driveror a data source driver, which is a program that makes connections to aspecific type of database. For some types of external databases (OLAPcubes), all you need to do to retrieve the data is to set up a data source.How Query uses data sources After you set up a data source for aparticular database, you can use it whenever you want to create a queryto select and retrieve data from that database— without having to retypeall of the connection information. Query uses the data source to connectto the external database and show you what data is available. After youcreate your query and return the data to Excel, Query retrieves the dataand provides the Excel workbook with both the query and data sourceinformation so you can reconnect to the database when you want torefresh the data.A data source is the means through which Query and Excel connect to aspecific database and retrieve data.Return to top
Defining your queryUse the Query Wizard for most queries The Query Wizard, which ispart of Microsoft Query, is designed for users who are new to creatingqueries. The wizard makes it easy to select and bring together data fromdifferent tables and fields in your database. After you select the data, youcan also use the wizard to filter and sort the results of your query. Youcan use the Query Wizard either to create a complete query or to start aquery that you make further changes to directly in Query.Work directly in Query for other types of queries If you are familiarwith creating queries or you want to create a more complex query, youcan work directly in Query. You can use Query to view and changequeries you start creating in the Query Wizard, or you can create newqueries without using the wizard.Use Query directly when you want to create queries that do the following:Select specific data from a field In a large database, you might want tochoose some of the data in a field and omit data you don't need. Forexample, if you need data for two of the products in a field that hasinformation for many products, you can use criteria to select data for onlythe two products you want. Learn about using criteria.Retrieve data based on different criteria each time you run the queryIf you need to create the same Excel report or summary for several areasin the same external data— such as a separate sales report for eachregion— you can create a type of query called a parameter query. Whenyou run a parameter query, you are prompted for the data to use as thecriteria when the query selects records. For example, a parameter querymight prompt you to enter a specific region, and you could reuse thisquery to create each of your regional sales reports. Learn aboutparameter queries.Combine data in different ways You can use Query to combine, or join,the data from different tables in your database. For example, if you havea table of product sales information and a table of customer information,you might join these tables in ways that show which customers have notmade purchases recently. Learn about joining tables.
Creating an OLAP cube from the data in a query On-Line AnalyticalProcessing (OLAP) is a way of organizing data for querying and reportinginstead of processing transactions. In an OLAP database, data isorganized hierarchically and stored in cubes instead of tables.You can use Query to create an OLAP cube from the data in a query sothat it takes less time and effort to create Microsoft Excel reports basedon the data. To create an OLAP cube, you run the OLAP Cube Wizardfrom the Query Wizard or directly from Query. You can return the data toExcel as a PivotTable report. Learn about creating an OLAP cube fromqueried data.
More informationWhen to use the Query Wizard versus Microsoft QueryReturn to top
Working with the data in Microsoft ExcelAfter you create a query in either the Query Wizard or Microsoft Query,you can return the data to a Microsoft Excel worksheet. The databecomes an external data range or a PivotTable report that you canformat, analyze, and refresh.Formatting and analyzing the data In Excel, you can use familiar toolssuch as the Chart Wizard or automatic subtotals to present andsummarize the data. You can format the data, and your formatting isretained when you refresh the external data from the source database. Ifyou add columns of formulas to the external data range, the formulas canbe copied automatically to any new rows that are added to the rangewhen you refresh the data. You can use your own column labels insteadof the field names, and add row numbers automatically. For informationabout controlling the formatting and layout of an external data range, seeExcel Help.Reusing and sharing queries In both the Query Wizard and Query, youcan save a .dqy query file that you can modify, reuse, and share. Excelcan open .dqy files directly, which allows you or other users to createadditional external data ranges from the same query.If you want to share an Excel summary or report that is based on externaldata, you can give other users a workbook that contains an external datarange, or you can create a report template. A report template lets yousave the summary or report without saving the external data so that thefile is smaller. The external data is retrieved when a user opens the reporttemplate. For information about creating report templates, see ExcelHelp.Refreshing external data When you refresh external data, you run thequery to retrieve any new or changed data that matches yourspecifications. You can refresh a query in both Query and Excel. Excelprovides several options for refreshing queries, including refreshing thedata whenever you open the workbook and automatically refreshing it attimed intervals. You can continue to work in Excel while data is beingrefreshed, and you can also check the status while it's being refreshed.For information about refreshing external data in Excel, see Excel Help.
Return to top
Get program information about Microsoft QueryOn the Help menu in Microsoft Query, click About Microsoft Query.Program information, including the version number, appears in the AboutMicrosoft Query dialog box.
Use keyboard shortcuts in Microsoft QueryWhat do you want to do?Move in the Microsoft Query panes by using shortcut keysSelect data and criteria by using shortcut keysEdit data and criteria by using shortcut keysUse function keys in Microsoft QueryUse shortcut keys in the OLAP Cube Wizard
Move in the Microsoft Query panes by using shortcut keysSome of the content in this topic may not be applicable to somelanguages.
Move in the Table panePressToTABMove between tablesUP ARROW or DOWN ARROW Move to and select field names in atableSHIFT UP ARROW orSelect contiguous fields in a table or toSHIFT DOWN ARROWcancel selection
Move between columns and rows in the Data pane or theCriteria panePressF5, type the number of the recordin the Record box, and thenpress ENTERTAB, ENTER, or RIGHT ARROW(from a selected cell)SHIFT TAB, SHIFT ENTER, orLEFT ARROW(from a selected cell)ENDCTRL ENDHOMECTRL HOMEDOWN ARROWCTRL DOWN ARROWUP ARROWCTRL UP ARROWToMove to a specific record (row) in theData paneMove to the next column in the DatapaneMove to the previous column in thecurrent row or the last field in theprevious recordMove to the last column in the currentrowMove to the last column in the lastrowMove to the first column in the currentrowMove to the first column in the firstrowMove to the current column in thenext rowMove to the current column in the lastrowMove to the current column in theprevious rowMove to the current column in the firstrow
Move the insertion point within a value in a cell in the Datapane or the Criteria panePressF2ToCancel selection and display the insertion point in aselected cellRIGHT ARROW Move one character to the rightCTRL RIGHT Move one word to the rightARROWLEFT ARROW Move one character to the leftCTRL LEFTMove one word to the leftARROWENDMove to the end of the current line in single-line ormultiple-line valuesCTRL ENDMove to the end of the value in multiple-line valuesHOMEMove to the beginning of the current line in single-lineor multiple-line valuesCTRL HOMEMove to the beginning of the value in multiple-linevalues
Move between screens in the Data pane or the CriteriapanePressToPAGE DOWNMove down one screen in a panePAGE UPMove up one screen in a paneCTRL PAGE DOWN Move right one screen in a paneCTRL PAGE UPMove left one screen in a pane
Select data and criteria by using shortcut keysSelect values within a columnPressF2ToSelect the current value, or cancel selection anddisplay the insertion pointSelect the value in the next columnTAB, ENTER, orRIGHT ARROW(from a selected cell)SHIFT HOMEMake a selection in a value from the insertionpoint to the beginning of the lineSHIFT ENDMake a selection in a value from the insertionpoint to the end of the lineSHIFT RIGHTExtend selection or cancel selection of oneARROWcharacter to the rightCTRL SHIFT RIGHT Extend selection or cancel selection of one wordARROWto the rightSHIFT LEFTExtend selection or cancel selection of oneARROWcharacter to the leftCTRL SHIFT LEFT Extend selection or cancel selection of one wordARROWto the left
Select a rowPressSHIFT SPACEBARDOWN ARROWToSelect the current rowSelect the next row, if the current row isselectedUP ARROWSelect the previous row, if the current row isselectedSHIFT DOWN ARROWExtend or cancel selection down one rowSHIFT UP ARROWExtend or cancel selection up one rowSHIFT PAGE DOWNExtend selection down one screenSHIFT PAGE UPExtend selection up one screenCTRL SHIFT SPACEBAR Select the entire result set
Select a columnPressCTRL SPACEBARRIGHT ARROWToSelect the current columnSelect the column to the right, if the currentcolumn is selectedLEFT ARROWSelect the column to the left, if the currentcolumn is selectedSHIFT LEFT ARROW Extend or cancel selection left one columnSHIFT RIGHTExtend or cancel selection right one columnARROWCTRL SHIFT PAGE Extend selection right one screenDOWNCTRL SHIFT PAGE Extend selection left one screenUP
Edit data and criteria by using shortcut keysCopy, cut, paste, or deletePressToCTRL CCopy the current selection onto the ClipboardCTRL VPaste the contents of the Clipboard at the insertion pointCTRL XCut the current selection and copy it onto the ClipboardBACKSPACE Delete the current selection or the character to the left ofthe insertion pointDELETEDelete the current selection or the character to the right ofthe insertion point
Zoom in to edit in the Data panePressToSHIFT F2 Open the Zoom Field dialog box to enter values in smallinput areas in the Data pane more conveniently
Function keys in Microsoft QuerySome of the content in this topic may not be applicable to somelanguages.PressF1SHIFT F1, and then pressENTERF2ALT SHIFT F2 orSHIFT F12ALT F2 or F12SHIFT F2ToSwitch to HelpGet context-sensitive Help for thecurrently selected itemSelect or cancel selection of current valueSave a query with the Save commandSave a query with the Save As commandOpen the Zoom Field dialog box to entervalues in small input areas in the Datapane more convenientlyCTRL F4Close the active queryALT F4Quit Microsoft QueryF5, type the number of theGo to a specific record (row) in the Datarecord in the Record box, and panethen press ENTERF6Move to the next pane in the QuerywindowSHIFT F6Move to the previous pane in the QuerywindowCTRL F6Switch to the next open Query windowCTRL SHIFT F6Switch to the previous open QuerywindowF9Run the currently open query
Use shortcut keys in the OLAP Cube WizardUse these keys to complete Step 2 in the OLAP Cube Wizard.
To move a field from the Source fields list to theDimensions box1. Press the TAB key to select the Source fields list.2. Press the UP ARROW or DOWN ARROW key to select the fieldyou want to move.3. Press the TAB key to select the button, and then pressENTER.4. Repeat steps 1 through 3 to move other fields.
To move a field to a lower or higher level within adimension in the Dimensions box1. Press the TAB key to select the Dimensions box.2. Press the UP ARROW or DOWN ARROW key to select the fieldyou want to move.3. Press CTRL X.4. Press the UP ARROW or DOWN ARROW key to select whereyou want to move the field.5. Press CTRL V.
Show All
Install an ODBC driver so that you can access an externaldata sourceThe ODBC drivers provided by Microsoft with Microsoft Query are allinstalled when you install Query. See a list of ODBC drivers installed withQuery.If the external database you want to access is not supported by theseODBC drivers, you need to obtain and install a Microsoft Officecompatible ODBC driver from a third-party vendor, such as themanufacturer of the database. Contact the database vendor forinstallation instructions.Note OLAP databases do not require ODBC drivers. When you installQuery, drivers and support are installed to access databases that werecreated by using Microsoft SQL Server OLAP Services. To connect toother OLAP databases, you need to install a data source driver and clientsoftware. Learn about third-party OLAP providers.
Show All
Install OLAP providersTo connect to third-party products that provide OLAP data and services,you need to install OLAP provider software. This software includesadditional data source drivers and client software to allow you to connectto a third-party OLAP database on a server.To use the Microsoft Office features for working with OLAP data, thethird-party OLAP provider must be Office compatible. The provider mightor might not support creating and working with offline cube files. You cancheck for this support by creating a Microsoft Excel PivotTable reportfrom source data that's provided by the third-party OLAP database. In thePivotTable report, the Client-Server Settings command on thePivotTable menu (PivotTable toolbar) will be unavailable if the providerdoes not support offline cube files.For information about installing and using a third-party OLAP provider,consult your system administrator or the vendor for your OLAP product.After you install the provider, you can set up data sources in MicrosoftQuery to access the provider's databases.
Show All
Databases you can accessMicrosoft Office provides drivers that you can use to retrieve data fromthe following data sources:Microsoft SQL Server OLAP Services (OLAP provider) 7.0 and laterMicrosoft Access 2000 and laterdBASEMicrosoft FoxProMicrosoft ExcelOracleParadoxSQL ServerText file databasesNote You can use ODBC drivers or data source drivers from othermanufacturers to get information from other types of databases that arenot listed here, including other types of OLAP databases. For informationabout installing an ODBC driver or data source driver that is not listedhere, see the documentation for the database, or contact your databasevendor.
Show All
Specify a source to retrieve data from1. If the Choose Data Source dialog box is not displayed, clickNew Query .2. Do one of the following:To specify a data source for a relational database, text filedatabase, or Microsoft Excel list, click the Databases tab.To specify an OLAP cube data source, click the OLAP Cubestab. This tab is available only if you ran Microsoft Query fromMicrosoft Excel.3. Double-click New Data Source.4. In step 1, type a name to identify the data source.5. In step 2, click a driver for the type of database you are using asyour data source.If you don't see the database you want, you may need to installthe ODBC driver or data source driver for your data source.How?6. Click Connect, and then provide the information needed toconnect to your database.For relational databases, text file databases, and Excel lists, theinformation you provide depends on the type of databaseselected. You may be asked to supply a logon name, apassword, the version of the database you're using, thedatabase location, or other information specific to the type ofdatabase.For Microsoft SQL Server OLAP Services data sources, followthe steps in the Multi-Dimensional Connection Wizard.7. After you enter the required information, click OK or Finish toreturn to the Create New Data Source dialog box.8. If your database has tables and you want a particular one to bedisplayed automatically in the Query Wizard, click the box for
step 4, and then click the table you want.9. If you don't want to type your logon name and password whenyou use the data source, select the Save my user ID andpassword in the data source definition check box. If thecheck box is unavailable, see the database administrator todetermine whether this option has been disabled.When you complete these steps, the name of your data sourceappears in the Choose Data Source dialog box.
Show All
Set up a data source that uses the Microsoft Access driver1. If the Create New Data Source dialog box is not displayed,click New on the File menu, click the Databases tab, and thendouble-click New Data Source. Type a name for the datasource in step 1, and select Microsoft Access Driver in step 2.2. In step 3, click Connect.3. Under Database, click Select, and use the Select Databasedialog box to locate your database file.If you want Microsoft Query to open the database as read-only,select the Read Only check box.If you want Microsoft Query to be the only user of the databasewhen you use this data source, select the Exclusive check box.4. Click OK.If your database requires you to set additional connectionparameters, click Advanced. For information about whether youneed to change any of these settings, see the administrator foryour database. For information about the advanced settings,click Cancel, and then click Help in the ODBC MicrosoftAccess Setup dialog box.5. If you want Microsoft Query to access the system database,click Database under System database, and then click SystemDatabase. In the Select System Database dialog box, locateyour system database file, and then click OK.6. Click OK, and enter your logon name and password underAuthorization. If you don't use a logon name or password togain access to the database, leave the boxes underAuthorization blank.Note You can use the ODBC Microsoft Access Setup dialog box tocreate, repair, and compact Microsoft Access databases. If you are notfamiliar with these operations, you should perform them only from withinMicrosoft Access. For more information, see Microsoft Access Help.
Show All
Set up a data source that uses the Microsoft dBASE orFoxPro driverBefore proceeding with these steps, it's a good idea to place all of thedatabase files that you want to use together in the same folder, so thatQuery can find all of your files and you don't have to type the full pathwhenever you want to add a table to a query. If you want, you can placethe database files in one folder and the index files in another folder.1. If the Create New Data Source dialog box is not displayed,click New on the File menu, click the Databases tab, and thendouble-click New Data Source. Type a name for the datasource in step 1 and select Microsoft dBase Driver orMicrosoft Visual FoxPro Driver in step 2.2. In step 3, click Connect.3. Do one of the following:For the Microsoft dBase Driver, click the version of dBASE orFoxPro that was used to create your database files in theVersion list. If the database files are not in the current folder,clear the Use current directory check box, and then clickSelect Directory to locate the folder. If the index files for thedatabase are also in a different folder, click Select Indexes tolocate the folder.For the Microsoft Visual FoxPro Driver, click the option for yourFoxPro database under Database type, and then enter the pathto the database in the Path box.
Show All
Set up a data source that uses the Microsoft Excel driverBy using the Microsoft Excel ODBC driver, you can access data onlyfrom workbooks in the same folder. Before proceeding with the followingsteps, copy all workbooks from which you want to access datasimultaneously into one folder, and name each worksheet range that youwant Microsoft Query to recognize as a table.You must set up a separate data source for each folder from which youwant to access data.Before you set up a data source or run a query to access data in anExcel workbook, close the workbook in Excel.1. If the Create New Data Source dialog box is not displayed,click New on the File menu, click the Databases tab, and thendouble-click New Data Source. Type a name for the datasource in step 1, and select Microsoft Excel Driver in step 2.2. In step 3, click Connect.3. In the Version box, click the Excel version that was used tocreate the workbook. If your workbook was created with Excel97, Excel 2000, or a later version of Excel, click Excel 97-2000.If your workbook was created with Excel 95 (Excel version 7.0),click Excel 5.0/7.0.4. Click Select Workbook, and then locate the workbook file.
Show All
Set up a data source that uses the Microsoft ODBC forOracle driverBy using the Microsoft ODBC for Oracle driver, you can connect toOracle databases that support SQL*Net 2.3 or later. For informationabout this support, see your Oracle documentation.Before you can set up data sources that use this driver, you must installOracle client and networking components version 7.3 or later. Thesecomponents are available from Oracle or your Oracle vendor.1. If the Create New Data Source dialog box is not displayed,click New on the File menu, click the Databases tab, and thendouble-click New Data Source. Type a name for the datasource in step 1, and select Microsoft ODBC for Oracle Driverin step 2.2. In step 3, click Connect.3. Under Database, click Select.4. In the User name box, enter your database logon name.5. Enter the name of your Oracle server and any additionalinformation necessary to connect to the server. See your Oracledatabase administrator for this information. For informationabout setup options, click Help in the setup dialog box.6. Enter your logon ID and password, and then click OK twice.
Show All
Set up a data source that uses the Microsoft ParadoxdriverBefore proceeding with these steps, it's a good idea to place all of thedatabase files that you want to use together in the same folder, so thatQuery can find all of your files and you don't have to type the full pathwhenever you want to add a table to a query.1. If the Create New Data Source dialog box is not displayed,click New on the File menu, click the Databases tab, and thendouble-click New Data Source. Type a name for the datasource in step 1 and select Microsoft Paradox Driver in step 2.2. In step 3, click Connect.3. In the Version box, select the version of Paradox that was usedto create your database files.4. If the database files are not in the current folder, clear the Usecurrent directory check box, and then click Select Directory tolocate the files.5. If your database requires you to set advanced options, clickOptions. For information about whether you need to changeany of these settings, see the administrator for your database.For information about the option settings, click Help on theODBC Paradox Setup dialog box.
Show All
Set up a data source that uses the Microsoft SQL Serverdriver1. If the Create New Data Source dialog box is not displayed,click New on the File menu, click the Databases tab, and thendouble-click New Data Source. Type a name for the datasource in step 1 and select SQL Server in step 2.2. In step 3, click Connect.3. In the Server box, enter the name of the SQL Server on yournetwork. If you want information about the selections availablefor Server, click Help.4. Click Options, and set any other options required for yourdatabase. For information about whether you need to changeany of these settings, see the administrator for your database.For information about the option settings, click Help.5. Enter your logon ID and password, and then click OK twice.
Show All
Set up a data source that uses the Microsoft SQL ServerOLAP Services driverYou can use this driver to set up data sources for both server databasesand offline cube files (*.cub) that were created from Microsoft SQL ServerOLAP Services databases with the Offline Cube Wizard in MicrosoftExcel or that were created from relational database records with theOLAP Cube Wizard in Microsoft Query. Learn about OLAP data sources.Note This topic explains how to set up data sources that use the OLAPprovider for the first version of Microsoft SQL Server OLAP Services(version 7.0 of MSMDCUBE.DLL). To set up data sources for laterversions of this provider, you can use the Data Connection Wizardinstead of Query. For additional information about setting up these datasources, see the Help for your version of Microsoft SQL Server OLAPServices.1. If the Create New Data Source dialog box is not displayed,click New on the File menu, click the OLAP Cubes tab, andthen double-click New Data Source. Type a name for the datasource in step 1 and select Microsoft OLE DB Provider forOLAP Services in step 2.The OLAP Cubes tab is available only when you run Queryfrom Excel.2. In step 3 of the Create New Data Source dialog box, clickConnect.3. In step 1 of the Multi-Dimensional Connection dialog box, doone of the following:To connect to a server database, click Analysis server, andthen supply the server name, logon ID, and password for yourserver. For information about these and other settings for yourdatabase, see your server administrator.To connect to an offline cube file, click Cube file, and enter thepath to the .cub file in the File box.4. Do one of the following:
If you're connecting to an offline cube file, click Finish.If you're connecting to a server database, click Next, select theserver database, and then click Finish. In step 4 of the CreateNew Data Source dialog box, select the name of the databasecube that you want to retrieve data from.
Show All
Set up a data source that uses the Microsoft text driverYou'll need to set up a separate data source for each folder that containstext files from which you want to access data. If you want to use the datasource to access data simultaneously from more than one text file, copyall of the text files to the same folder before proceeding with these steps.1. If the Create New Data Source dialog box is not displayed,click New on the File menu, click the Databases tab, and thendouble-click New Data Source. Type a name for the datasource in step 1 and select Microsoft Text Driver in step 2.2. In step 3, click Connect.3. If the text files are not in the current directory, clear the Usecurrent directory check box, and then click Select Directory tolocate the folder.Note You can gain access to data in text files in several formats,including columns separated by blank space or by commas or tabcharacters. For more information about the text file formats that you canuse, click Help in the ODBC Text Setup dialog box.
Show All
About creating a new queryA query is a means of finding the records that answer a particularquestion you ask about the data stored in a database. When you create aquery, you can either use the Query Wizard or work directly in MicrosoftQuery. How you create your q
allows Microsoft Excel and Microsoft Query to connect to an external database. When you use Query to set up a data source, you give the data source a name, and then supply the name and location of the database or server, the type of database, and your logon and password information. The information also includes the name of an ODBC driver
