Excel VBA Introduction Part 58.26 - SQL for Excel Files - Nested Subqueries

By Andrew Gould

If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can join this channel to get access to perks:

This video shows you how to nest multiple levels of query to answer more complicated questions about your data. You'll build on what you learnt in the previous lesson on basic subqueries to nest queries up to four levels (and up to fifty if you really want to!).

Chapters
00:00 Topic List
00:31 The Basic Setup
02:06 A Reminder of Basic Subqueries
03:02 Using Subqueries in the Where Clause
04:21 Nesting a Subquery in a Subquery
05:32 Changing the Example
07:07 Nesting Queries to Three Levels
09:10 Rearranging the Code
10:37 Nesting Queries to Four Levels
11:57 Adding More Criteria with Subqueries
13:52 Returning Multiple Values from a Subquery
15:03 Using the IN Operator with Subqueries

[aperxmim]

Please keeping making more advanced and complex subqueries, many of us will need to use these nested queries, I know I needed them.

[tejamarneni]

I can't thank you enough for the amazing series Andrew. You are the best.

[frikduplessis]

Thank you Andrew another blockbuster, and a brilliant tool for anyone's tool kit definitely not a low budget series, 👌

[rajeshmajumdar]

Thank you another blockbuster. You R the best!!!

[fahrigafarzade]

Thank you very much Andrew, I am learning a lot from your videos and closely following each of the newly relased ones.


I wanted to ask you something because I cannot find a solution to my problem for a while.



I compile daily FX swap deals from the Bourse of Istanbul Swap Market which has opened at 1 October 2018.

I have a table which consists of 11 columns and update it every business day.



These columns are "Deal Date", "Value Date", "Maturity Date", "Maturity (Days)", "Swap Point", "Exchange Rate", "TL Amount of Deal", "FX Amount of Deal", "Number of Contracts" and "Net TL Rate".



I can create reports like the total volume of outstanding deals made in USDTRY and EURTRY pairs at a businness date. I can query for the total volume of deals that have been made in USDTRY pair for all previous business dates.



However, my problem is that I want to query for "total volume of outstanding" deals for every "Value Date" for example for USDTRY currency pair.



In order to define a deal as "outstanding" on "today's date" and to include it in the aggregation; its "Value Date" should be equal to and less than "today's date" and its "Maturity Date" should be greater than "today's date".


Hence, I would like to write a query which will give me the "total volume of outstanding deals" for each and every date from the very first day of the market.



I would really aprreciate your help and can send you my compiled data. Btw, I am not working anywhere right now, its just for my personal interest. I would like to say that it was you and your videos that sparked interest in me to learn VBA and thanks to your videos I have developed myself so well that I have previously landed on a job just beacuse of my VBA skills.

Love and Regards from Turkey :)

[senlino]

Thanks for sharing this lesson!:) It's also interesting why IN clause is so much slower than WHERE clause. I had the same problem and, in my case, the IN clause was causing Excel to break or in many cases the calculation took much longer time than acceptable. As a workaround I usually insert sub-query results in a temporary worksheet so that I have ready to use data there.