Microsoft Access 2021: Advanced Queries

Name: Microsoft Access 2021: Advanced Queries
Author: John Varan

Learn how to use subqueries and the four major types of joins.

Preview the first lesson free — get full access to all 7 lessons.

Course: On-Demand

Hello, welcome to Knowledge City's course on Microsoft Access 2021 Intermediate. My name is John Veron, and in these lessons, you will learn about advanced queries. In this lesson, I will show you how to query with calculated values. Worrying with calculated values means writing queries that return values, which are not in the tables being queried. They will be calculated when the query runs. There are a couple of different ways we can do this in Microsoft Access, but before we do that, we need to create a table which we can use for the examples. Let's go ahead and create a table called Products, and let's give it names, field names of name, which will be a product name, and that will be short text. And then we'll have one called quantity, type number, unit, price, type, currency. All right, let's go to datasheet view, and let's populate a few Rosa data, maybe like four we'll have product A at quantity 10, unit cost $87, this should be enough for examples. Let's make sure and save this table, and then we can close it. The first way we can write a query using calculated values is with the query designer. So let's go to the create menu and go to the query designer, and let's add our product table. In the first column, let's just add the name column so we can see the name of the product. For the second column, instead of choosing a column from the list, we're simply going to type in a formula. So we will do quantity times unit price. This will tell access to multiply the values in both of those columns and then show the result. Let's click run and see if that works. That looks good. The calculated part seems to work, but let's give that result column a better name. Switch back to design view, and where it put in a default name, expr1, which stands for expression one, let's change it to total price. Make sure you keep that colon after it, that's required. Now let's save this query, and then let's run it. That looks a little better. The second way we could do this query is directly in SQL. So let's create another query, and we'll close this and switch the SQL view. First, let's write the query without the calculated portion and make sure it works. Then we can add the calculated part in. So let's start with select the name of our product from product. Nice and simple. We run it. We get our products. It seems to work. Let's go back to SQL view. Now we're going to add another field to select after name, so we need a comma. Then we put in what field we're going to select. This works just like in the query designer view, so we do quantity times unit price, and then we want to rename this. So we use the as keyword as total price here. If you use a column name that has a space in it, we have to put it in brackets in the query designer. We don't because it has a colon after it, so make sure you keep that difference in mind. I forgot to put a space in our unit price, and we run it, and you can see that it works. These are the basics for how you would create calculated values and queries. Of course, your formulas can get more complicated, but the general principle stays the same. Thanks for watching. Stay tuned for the next lesson where I will show you how to work with sub queries.

Intermediate Provider : John Varan 7 Lessons · 29m in English

Course Description

The difference between well-managed data and a confusing, disorganized database is the ability to write efficient queries. Subqueries enable you to write complicated queries in a simple way, opening up possibilities for even more sophisticated, powerful queries. Effectively executed queries can perform value calculations, join tables, and incorporate external data sources. They’re an essential tool for organizing your data for the best possible results.

Delve into a more detailed understanding of queries with this module. You’ll learn about more advanced ways to query data, such as writing subqueries and how to create relationships between your tables. We’ll go over the different types of joins to best support the needs of your queries, and you’ll be introduced to the four basic types of joins: outer join, inner join, full join, and cross join. Finally, we’ll explore how to write queries that can calculate values and how to query external data sources.

What You'll Learn

Write queries with calculated values
Query external data sources
Join tables in different ways to support your queries
Create subqueries to simplify complex queries
Apply the four basic join types: inner, outer, full, and cross joins

Key Takeaways

Subqueries enable you to write complicated queries in a simple way, opening up possibilities for more sophisticated, powerful queries.
Effectively executed queries can perform value calculations, join tables, and incorporate external data sources.
There are four basic types of joins: outer join, inner join, full join, and cross join, each chosen to best support the needs of your queries.
Creating relationships between tables and using different join types helps organize data for the best possible results.
Queries can be written to calculate values and to query external data sources.

Frequently Asked Questions

What will I learn in this course?

You will learn more advanced ways to query data, including writing subqueries, creating relationships between tables, using the different types of joins, writing queries that calculate values, and querying external data sources.

Which types of joins does this course cover?

It introduces the four basic types of joins: outer join, inner join, full join, and cross join, and explains how to choose joins to best support the needs of your queries.

What topics are included in the lessons?

The lessons cover Querying With Calculated Values, Subqueries, Inner Joins, Outer Joins, Full Joins, Cross Joins, and Querying External Data.

What skills does this course help develop?

It helps develop skills in Access Query Languages, Data Access, Extended SQL, Microsoft Access, Microsoft Data Access Components, and Query Understanding.

Why are subqueries useful?

Subqueries enable you to write complicated queries in a simple way, opening up possibilities for even more sophisticated, powerful queries.