Excel VBA Introduction Part 58.20 - SQL for Excel Files - Outer Joins

[@Marrobones1985]

As someone who knows most of these things already it is great to see the value you are putting out there to others. I wish I found something like this when I was learning sql and vba. Keep up the good work.

[@WiseOwlTutorials]

Thank you so much for the kind words! Apologies that we weren't around sooner 😀

[@andrewwest5344]

Great. I only discovered you yesterday and now I look deeper what you have here. You are a genius lol!

[@WiseOwlTutorials]

:D far from a genius but I appreciate the kind words! Thanks Andrew!

[@janezklun]

The best vba on yt, thank you Andrew for sharing such great explanations, Cheers

[@WiseOwlTutorials]

Thanks so much Janez!

[@KhalilYasser]

The most and the best amazing tutorials on KZbin are here on your fantastic channel. Thank you very much.

[@WiseOwlTutorials]

My pleasure Yasser, thank you for all your support!

[@tejamarneni]

Hi Andrew, thank you so much for the Amazing videos. I got a call back from Amazon. They called me for second round. This time I passed the first round. As I mentioned the first round was basic. They asked me questions on GROUP BY, DENSE_RANK() and delete duplicate records. They said the second round will be more technical. I still have to set a time for second. Thank you so much for the amazing videos. I learned SQL by watching your videos.

[@WiseOwlTutorials]

That's great news Ravi, congratulations! Time to study and get ready for the next round! Fingers crossed for you 🤞

[@tejamarneni]

@@WiseOwlTutorials Thank you, Andrew. I couldn't have done it without your help. My second round is on July 14th. I am rewatching your videos and solving leetcode problems. So that I don't miss any concepts and boost my confidence.

[@WiseOwlTutorials]

@@tejamarneni It's your own hard work that has taken you this far. Keep going!

[@youssefsedkey7616]

Thanks, Andy, I really enjoyed this tutorial.

[@WiseOwlTutorials]

My pleasure Youssef, thank you as always!

[@daves4026]

Omg awesome for years I thought the ado sql was too limited but it’s really good. Top drawer explanations ‘hats of to you’ please do more on this subject 👍

[@WiseOwlTutorials]

Cheers Dave, glad you liked it!

[@onurtunc9468]

i spent 4 days to make sum with two left join. at 17:54 min i saw the correct syntax of query. thank you so much

[@WiseOwlTutorials]

You're very welcome, I'm glad that it helped!

[@lumachinomenolattola]

Thanks for your tutorials

[@WiseOwlTutorials]

You're very welcome!

[@krn14242]

Thanks Andrew.

[@WiseOwlTutorials]

My pleasure Kevin!

[@frikduplessis8849]

Thank you again Andrew for a awesome tutorial, I was struggling with JOINTS before this tutorial now its clear, however I will have to recheck it again my dog 🐕 wants all my attention today 😀

[@WiseOwlTutorials]

😀 of course the dog must take priority! Thank you for the support as always!

[@sudiptobanerjee7005]

Really appreciate what you are doing . Really hope that someday this channel becomes really famous. You deserve this. 👏 . Can you also provide a video of how to extract data from PDF from excel 2016 ?

[@WiseOwlTutorials]

Thank you so much! I have some questions on my list about working with PDFs with VBA but I don't have much experience with Adobe products so I don't know when or if I'll be creating videos on this topic, sorry!

[@Munichandra_Reddy]

Hi sir. When will you start creating topics , please inform me ...? Sorry for the asking.......... Previous day's I asked one question you had not explained .

[@munichandrakuppani8103]

Sir, No reply ,because of excitement

[@corneliuzabolotnic]

Hello once again WiseOwl! Thanks once again for the great videos. I have a question wich is how to do a Inner Join (or Outer Join) with an Excel Sheet and an Access Table? Is the only viable option to loop through all Excel rows and get the info from the Access DB? What I was aiming was to use an SQL statment to join the 2 tables from different locations (which I assume it's a hard task to do). Thanks!

[@WiseOwlTutorials]

Hi Corneliu! This is surprisingly easier than you might expect. You can use the IN clause to reference other files (we used this technique in one of the UNION videos to add multiple workbooks and csv files to the same list and we'll have a video tomorrow on joining worksheets from different files). To join a worksheet with a table in an Access database you can do this: SQLQuery = _ "SELECT " & _ "f.[Title] " & _ ",f.[Release Date] " & _ ",g.[Genre]" & _ "FROM " & _ "[Film$] AS f " & _ "INNER JOIN (SELECT * FROM [Genre] " & _ "IN 'C:\My Folder\Movies.accdb') " & _ "AS g ON f.[Genre ID] = g.[GenreID] " I hope it helps!

[@corneliuzabolotnic]

@@WiseOwlTutorials Once again, I don't know how to thank you. Thank you very much. I tried that option. The problem was that in the Access DB, the format was in number and in the Excel the format was Text. 😂 Spent one day trying to find the solution.😅

[@WiseOwlTutorials]

@@corneliuzabolotnic No problem! And don't worry, I've spend many hours on similar problems!

[@daves4026]

How do you create your entity diagrams please? as I have developed a VBA tool which creates DDL's but would like to translate those DDL's into entity diagrams for clients. Any suggestions on how I might go about this without buying a license would be much appreciated

[@WiseOwlTutorials]

Hi Dave! It's just a quick mock-up created manually on a worksheet using cells and a few drawing objects. It could have been done with VBA but it didn't seem worth the effort for a one-off. You're very welcome to download the file using the link in the video description if you'd like to poke around the diagram!

[@youssefsedkey7616]

Question: How do I put the serial number column at the beginning of the query result? I can do this with Excel VBA after query, but I want to do it with SELECT CLAUSE query. thanks, advanced dear.

[@WiseOwlTutorials]

Hi Youssef! Sadly there isn't a convenient way to do this using ADO SQL (if we were using T-SQL we could just use the ROW_NUMBER function!). Here are some ideas that you could try stackoverflow.com/questions/17279320/row-numbers-in-query-result-using-microsoft-access/17303172 I hope that one of them helps!

[@youssefsedkey7616]

​@@WiseOwlTutorials I opened the link and read the whole page and tried all the solutions but nothing worked except the following method, It worked well when I do not use WHERE with it SELECT *, DCount("[ID]","[mytable]","[ID]

[@WiseOwlTutorials]

@@youssefsedkey7616 That's interesting information Youssef. Thank you very much for taking the time to test this and let us know!