Work directly in Microsoft Query when you want to create queries that do the following:. Select specific data from a field In a large database, you might want to choose some of the data in a field and omit data that you don't need.
For example, if you need data for two of the products in a field that contains information for many products, you can use criteria to select data for only the two products that you want.
Retrieve data based on different criteria each time you run the query If you need to create the same Excel report or summary for several areas in the same external data — such as a separate sales report for each region — you can create a parameter query. When you run a parameter query, you are prompted for a value to use as the criterion when the query selects records.
For example, a parameter query might prompt you to enter a specific region, and you could reuse this query to create each of your regional sales reports.
Join data in different ways The inner joins that the Query Wizard creates are the most common type of join used in creating queries. Sometimes, however, you want to use a different type of join. For example, if you have a table of product sales information and a table of customer information, an inner join the type created by the Query Wizard will prevent the retrieval of customer records for customers who have not made a purchase. Using Microsoft Query, you can join these tables so that all the customer records are retrieved, along with sales data for those customers who have made purchases.
Reusing and sharing queries In both the Query Wizard and Microsoft Query, you can save your queries as a. Excel can open. The Choose Data Source dialog box is displayed. In the Choose Data Source dialog box, click the Queries tab.
If you want to open a saved query and Microsoft Query is already open, click the Microsoft Query File menu, and then click Open. If you double-click a.
If you want to share an Excel summary or report that is based on external data, you can give other users a workbook that contains an external data range, or you can create a template. A template allows you to save the summary or report without saving the external data so that the file is smaller. The external data is retrieved when a user opens the report template. After you create a query in either the Query Wizard or Microsoft Query, you can return the data to an Excel worksheet.
The data then becomes an external data range or a PivotTable report that you can format and refresh. Formatting retrieved data In Excel, you can use tools, such as charts or automatic subtotals, to present and to summarize the data retrieved by Microsoft Query. You can format the data, and your formatting is retained when you refresh the external data.
You can use your own column labels instead of the field names, and add row numbers automatically. Excel can automatically format new data that you type at the end of a range to match the preceding rows.
Excel can also automatically copy formulas that have been repeated in the preceding rows and extends them to additional rows. Note: In order to be extended to new rows in the range, the formats and formulas must appear in at least three of the five preceding rows. In the Editing options section, select the Extend data range formats and formulas check.
To turn off automatic data range formatting again, clear this check box. Refreshing external data When you refresh external data, you run the query to retrieve any new or changed data that matches your specifications. You can refresh a query in both Microsoft Query and Excel. Excel provides several options for refreshing queries, including refreshing the data whenever you open the workbook and automatically refreshing it at timed intervals. You can continue to work in Excel while data is being refreshed, and you can also check the status while the data is being refreshed.
For more information, see Refresh an external data connection in Excel. Do one of the following: To specify a data source for a database, text file, or Excel workbook, click the Databases tab. The Create New Data Source dialog box is displayed. In step 1, type a name to identify the data source.
In step 2, click a driver for the type of database that you are using as your data source. Notes: If the external database that you want to access is not supported by the ODBC drivers that are installed with Microsoft Query, then you need to obtain and install a Microsoft Office-compatible ODBC driver from a third-party vendor, such as the manufacturer of the database.
Important: Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Back to home. Getting Help. How do I? How do I create a Microsoft Query from Excel? This article explains an easy way to create a Microsoft Query from Excel. Users who are hosted may may need to contact the hosting provider for assistance. Do you have the data in a table or just a filtered list? Then Add the columns lastname, firstname, phonenumber to the rows section. Then Add Phone number to the filter section and filter out the null values.
Now Sort like normal. I suggest you to have a look at the MySQL csv storage engine which essentially allows you to load any csv file easily created from excel into the database, once you have that, you can use any SQL command you want.
For example, from a osgeo4w shell in the same directory as the spreadsheet, use the ogrinfo utility:. Or run the same query using ogr2ogr to make a simple CSV file:. To do similar with older. Microsoft Access and LibreOffice Base can open a spreadsheet as a source and run sql queries on it.
That would be the easiest way to run all kinds of queries, and avoid the mess of running macros or writing code. Excel also has autofilters and data sorting that will accomplish a lot of simple queries like your example.
If you need help with those features, Google would be a better source for tutorials than me. How are we doing? Please help us improve Stack Overflow. Take our short survey. Stack Overflow for Teams — Collaborate and share knowledge with a private group.
Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. How to run a SQL query on an Excel table? Ask Question. Asked 8 years, 2 months ago. Active 4 months ago. Viewed k times. Improve this question. TylerH Vap0r Vap0r 2, 2 2 gold badges 20 20 silver badges 35 35 bronze badges. Do you want to do be doing this in SQL itself, or from within an application? I have investigated this quite thoroughly and have written up findings at exceldevelopmentplatform.
Add a comment. Active Oldest Votes. Further notes By default, it is assumed that the first row of your Excel data source contains column headings that can be used as field names. Improve this answer. Andri Busch 6 6 bronze badges. The data connection requires a file name, which is a show-stopper when the task is to query the current file.
Moreover, a query cannot be used the same way a formula can, you can only use a fixed query to populate a range; and it's not updated automatically.
0コメント