So the video is all about how to combine multiple tables into a single list and we'll begin with a simple example of union in two tables and explain the difference between union.
And union all we'll explain how you can sort the results of your union queries and the importance of matching the numbers of columns in each select.
Statement in the query we'll explain how to map columns which have different names in your different select statements how to add calculated columns and to.
Finish the video off how to add criteria to the results of each select statement so let's get started if you've been following along with.
Previous parts of this series you'll be fairly familiar with the basic setup by now so you may prefer just to skip to the next chapter of this video.
Which allows us to run a query by clicking this obviously labeled button and when we do that it's going to extract some information from a separate excel file called movies.
The movie's workbook for this video has a couple of different worksheets with various tables containing information about films released in different years.
I have both of those files stored in the same folder and i'll drop a link in the video description so that you can download the starting files and follow along and write the code if you'd like.
To i'm going to leave the movie's workbook open for the duration of the video but you're very welcome to keep the movie's workbook closed and all of the code will.
Still work the code in the basic union queries macro enabled workbook relies heavily on microsoft activex data objects.
Which isn't something i'm going to talk
About much in this video we've covered that in a previous series and i'll point you to this playlist and if you're interested in the activex data.Object side of things then i'd recommend starting with the how do i get data from a closed excel file using vba video just to show you the basic code working.
If i head back to the basic union queries workbook if i head into the visual basic editor i've already written a bunch of code that will.
First of all create a simple select statement this one's going to select data from a named range called films2020 and once we've constructed that it's.
Going to get passed into a separate subroutine which deals with the complicated stuff such as establishing the connection to the movie's workbook finding that table.
Of data and extracting it into a record set our query gets used at this point here to set the source property of the record set object.
And then the rest of that code deals with writing out the content of the record set into the worksheet applying some formatting and tidying things up.
So if i head back to my menu sheet in the basic union queries workbook click the run query button i get the table of films from the 2020.
Highest grossing films table on the film year sheet in the movies workbook so i've run my query once and that's extracted the list of films.
Stored in the films 2020 named range in
The movie's workbook so refer to films 2020 in our select statement having a look back at the movie's.Workbook on the film year sheet we've got a range name called films 2020 consisting of the table of data we've just extracted now the next obvious step will be to.
Select the films 2019 table and the 2018 and 2017 and store those together or output those together in one single continuous list rather than as separate tables.
Now in order to do that i can head back to the visual basic editor and then i can concatenate a new line to the end of my current select statement i can't just write the word select by.
Itself if i wanted to add another select statement to the same list of outputs i need to union the second select statement to the first from that point everything is exactly.
The same however so i can copy and paste in fact just to save a little bit of time select star or asterisk to select all of the columns.
From films 2019 having done that if i head back to my menu sheet in the basic union queries workbook i can click the run query button and.
That will produce a list consisting of all of the films from the 2020 table and all of the films from the 2019 table just before we move on and start.
Including the other tables in the same union query it's worthwhile understanding the difference between union and union all so we've written a union query which.
Automatically scans for and removes duplicate rows in your output as it turns out because there are no duplications in rows between the 2020.
And the 2019 tables we get all 20 results but just to demonstrate that the union query does remove duplicates we're going to change our select statement.
To try to select two copies of the 2020 sheet or the 2020 range name i should say so with the union keyword all by itself if we head back to the menu sheet.
And we run that query we'll find that we only get 10 rows and that's because all 10 rows that we've tried to select in the second select statement.
Are exact duplicates of the first now watch what happens if we head back to the visual basic editor and include the all keyword after the union keyword.
If we then head back to the menu sheet and run that query again we get 20 results two sets of the same 10 rows of course because we've selected from the same table but just to.
Demonstrate that the all keyword does not remove duplicates now even if you're not expecting duplicates in your output it's still worthwhile considering using.
The all keyword so if i select union all from films 2019 there will be no difference in the number of results that i get.
But because the union or keyword doesn't have to scan for duplicates you may find a small performance benefit to using the all keyword even when you don't need to so one last.
Time if we head back to the menu sheet we can run that query again and we get the same 20 results but this time using union all rather than using union.
One small difference you might notice in the results of the union and union all queries is that the rows are sorted differently so this sheet shows the list of results.
Generated by the union or query and if i switch back to sheet 2 which is generated by the union query you can clearly see the order is different now as always if you want to.
Guarantee a sort order of your results you should always add an order by clause to the query so let's say we wanted to make sure that our films were sorted in descending order of.
Worldwide gross so the most successful film financially sits at the top of the list we can do that by adding an order by clause of the query if we head back to.
The visual basic editor normally you add the order by clause to the end of each individual select statement that you write but in a union query or a union or query.
You add one single order by clause to the end of the entire list of select statements so i'm going to concatenate another continuation character to the end of the.
Last line and then open up some more double quotes add an order by which i'll attempt to spell correctly and then refer to the column name whose.
Values i want to sort by so that was worldwide gross i then want to say that that's in descending order so i'll add a d e s c to the end close the double quotes.
And then head back to the menu sheet run the query again and i'll see that i get the list of results this time in descending order of worldwide gross which is the order that.
I wanted from here we can simply add in the other tables to our query so we've got if i head back to the visual basic header editor a 2018.