Now, before moving on to the next step, let’s review quickly how your SQL would look if you were to write the conditions by hand for two sales orders:īasically, we need to be able to generate (salesordernumber = '' and year(orderdate) = )by each row we have in the Sales Order table. Let’s change the Year column data type to Text Format it as a table, we are going to name it as “Sales_Orders” Copy the list of values with the year and paste into a new spreadsheet.This selected the columns we wanted in the report, now we need to let Excel know which specific rows we want. Click OK and then load your results to a spreadsheet Write the SQL with all the columns that you will need in final the result and optionally, write “limit 10” at the end to avoid loading too many records (if SQL Server, you will have to use the TOP 10 statement). In the pop up, fill out with Server, Database and open the advanced options so you can paste/write your SQL code.Just go to Data -> Get Data -> Database – > MySQL (or SQL, Oracle, IBM depending on your database) Connect to your database Using the “Get and Transform Data” options.We’ll achieve this by writing a dynamic SQL statement that will build the WHERE conditions based on the list of values that we will have in the Requested Sales Orders table. But what if you receive these requests 2-3 times a week from several different users? Wouldn’t it be nice if you (or your end user) could just copy and paste the list of desired values into a table, and after refreshing the file see in the spreadsheet the new results within seconds?

We can use Excel to query the database and get the information requested, but you will have to manually write the WHERE conditions that include the Sales Order number and the year. You need to select from the DB based on a long list of specific records. If you regularly run queries to any database in your workplace, chances are you have encountered a user request like this: Use an excel table to modify your SQL query

Web queries excel 2016 mac