In this Walk Through

This section demonstrate how to use custom queries in an Excel OleDB DataView, to join sheets from a single Excel workbook.

To do this walk through you need:

  • An Excel workbook with data on several sheets referencing each other (see column Weather Code of sheet Weather and column Weather of sheet Survey below)

Writing the query

Custom queries allow you to specify which range to look at on the sheet, but also potentially to use named range and common SQL operations such as JOIN between multiple worksheets.

For example consider a workbook with the following 3 sheets: Countries, Weather, and Survey (the survey sheet containing 1 record per month per country):


   


Using the following query would return the Weather History for the 3 countries, by joining the 3 tables:


SELECT [Countries$].[Country Name] , [Survey$].[Date] , [Weather$].[Description] 

FROM

( [Survey$] INNER JOIN [Countries$] 

ON [Countries$].[Country Code] = [Survey$].[Country] )

INNER JOIN [Weather$] 

ON [Weather$].[Weather Code] = [Survey$].[Weather]


ALMOST LIKE SQL


The Syntax is pretty close to standard SQL, with slight variations to fit Excel requirements: note for instance the $ sign after each sheet name or the brackets around the first JOIN to enable the following JOIN.


Although the query result is pretty intuitive in this case we could also add an ORDER BY clause to sort by weather code and, maybe, reveal a weather trend...

At any point in time, we can test the query using the Test button.