14. Subqueries (Programming In Microsoft Access 2013) 🎓 

Looking for DAILY news and commentary? Join us on my other channel “Coffee With Steve” for daily videos where we discuss Technology, Software Development, Politics, Culture, and many other things. Coffee With Steve: ru-vid.com/show-UCeXAUvo5xxDY_b-lSknPC1A

Steve. I am so very grateful for the highly informative and candid (warts and all ;) ) approach. I have learned a great deal so far and looking forward to learning a great deal more. Thanks

Your videos are very concise. It's difficult to keep maintain my attention and you do a great job! I enjoy learning from you Steve Bishop. Have a like.

Great job. I learn at least one nugget of information every time. Well worth watching!

Good one. I find the builder helpful when you are doing concatenations.

Excellent description. That solves a problem I have been having with my db of displaying entries that have null values. Thank you

First of all thank you for this awesome series. Not only ne of the best Access Tutorials, it just is one of the best Tutorials I have ever seen! But iI have a Question.. Well i was just typing my Question here and noticed that its to hard to describe haha. Ill watch alle your videos and see if I can find the answer! Thank you

Can you explain why you wouldn't just change the join to show all addresses the only associated address types? I don't see why we would us a subquery?

Thanks a lot for the whole series 👍.

Great tutorial. Thx !

Hi Steve! You are a master. You solved my problem with the subquery stuff! It is the one I had! However, I have a question for you. So, I start to get used more and more to Access thanks to your tutorials, and more info that I can find on the web. I am building a subquery, but the info does not look I want. Here is my problem: Let's say I have a table with "References" and another one with "Authors". One reference has many authors and one author can appear in many references. So it is a many-to-many relationship. I build it with a third table that links the two or use lookup wizard with "allow several entry". Let's say I use the first method: I build a junction table that makes the like between references and authors. From that I can create a query (actually a subquery that will be used for something else) that links the references with authors. Building the query, it will appear something like that: ReferenceID Title Authors 1 Book1 Joe Dante 1 Book1 Jane Schmoe 1 Book1 Godzilla 2 Book2 Joe Dante 2 Book2 Monika Lebobsky ... Actually, for each book, the query will add a record for every author. My question is the following: is there a solution to "sum up" or "concatenate" (I do not know what is the correct thing to say), in order to have 1 line for each book with multiple values for authors separated by a coma? For example something like that? ReferenceID Title Authors 1 Book1 Joe Dante, Jane Schmoe, Godzilla 2 Book2 Joe Dante, Monika Lebobsky That would be awesome to do! And I would be very thankful to you if you can help me! I need to build that in order to use it as subquery to add information (mechanical tests, etc.) Do you know please how to do or do you know where I can find the information? Cheers!

You probably cover this in a later video. How do you create a query pulling the telephone information that will still show the customers even if they do not have a telephone number? given that the relationship is between the qrycustomer and Joining table? I Joined the address, phone number, phone type and the customer_phone which gave me the customers that had numbers. How do I show the all the customers, even those without numbers?

May I know how you select multiple columns from a table or a query and then drag them to new query design pane or whatever that bottom part of the window is design view?

Hi, At 3:00 when you added the new table and ran the query, I am getting a message "type mismatch in expression". I also selected option 1 for join properties. How can I fix this issue?

Hi, thanks for the great series. I am an experienced Access developer, but with a much older version of Access (don't ask...). I am in the habit of usually creating "double joins" in queries -- ie table 1 joined to table 2 joined to table 3 instead of saving and joining subqueries. is there an advantage (performance or otherwise) to defaulting to the subquery technique instead? Thanks!

I can't see any "SQL Query" here! Only wizard query!!

It looks like 'll have to change the form Recordset Type to Dynaset (Inconsistent Updates) when utilitizing Subqueries in a Query in order to EDIT Data in the query, .... I go online and see some people say it may be an issue when updating info. Should I be concerned ? I do I just test adding records to see if the updating works? I'd hate to create a database that could eventually crash and fail.

Thanks for this great series I finished it and restart, I have a question please and I want the answer to be simple and in-depth because I'm not an English native speaker. why did you use sub query?because I can make this idea without sub query using left and right join like this SELECT cus.cusName, cusAddresses.addressType_ID, cusAddresses.city FROM tblAddressType RIGHT JOIN (cus LEFT JOIN cusAddresses ON cus.ID = cusAddresses.cus_ID) ON tblAddressType.ID = cusAddresses.addressType_ID;

Hi Steve. When I run this query or the sub-query, it's only showing the first Metro Properties (not all three companies). Any idea what I may have done wrong to cause that? I'll keep watching and maybe figure it out. Thanks.

When you use a query in another query like was done in this segment it would be great if there were something we could call it but I don't think it's a good idea to call it a subquery. According to msdn.microsoft.com/en-us/library/office/aa217680(v=office.11).aspx A subquery is an SQL SELECT statement that's nested inside of another SQL statement." That's something entirely different from just using a query in another query.

Is there a way to create a query to with totals to show all records including the ones with a zero value?

Hi Sorry, stupid question probably but I am going to ask it anyway (be gentle with me I am new to all this). What turned the qryEmployeeAddresses from being a query into a subquery?

Also,, I am unable to add entries in the query or the form when I run the phone query