Lessons

Resources

Questions?

Please feel free to post your questions or comments below. If you have questions about a specific lesson, please specify the lesson number and the time index in the lesson where the issue occurs. Thank you!

Subscribe for Updates

If you would like to be notified whenever changes are made to this page, new lessons are posted, or comments are made, click on the subscribe button.

Subscribe to Access 223
Get notifications when this page is updated

Intro

In lesson 223, you will learn about advanced queries in Microsoft Access, including how to create crosstab queries that organize data with row and column headers, and how to use the Find Duplicates and Find Unmatched Query Wizards. We will cover advanced query properties like showing unique values and records, working with top values, and explore some helpful SQL basics. This lesson also includes additional tips and tricks for making the most of your queries. This is part 4.

Transcript

Welcome to Microsoft Access 223 Advanced Queries Part 4. Brought to you by accesslearningzone.com. I am your instructor, Richard Rost.

In today's class, we are going to learn about crosstab queries where you can generate queries that look and act like spreadsheets, where you have row headers and column headers and interesting data in the middle.

We are going to learn about the other query wizards: the Find Duplicates wizard and the Find Unmatched Query wizard. We will learn about some advanced query properties like showing unique values, unique records, the top 10 records, and a lot of other interesting stuff.

We will learn about some SQL statements, what they are, why you should know how to read and write SQL, and how it will help you with your databases. Plus, we have some other tips and tricks to round out our queries.

This series is a four-part series on advanced queries. There are lots of other little tips and tricks that I had in order to throw in, so I will throw those in today at the end of class.

Of course, this is the fourth part of our advanced queries series. This class follows Access 222. I strongly recommend taking 220, 221, and 222 before taking 223, and of course, all the 100 and early 200 level stuff before that.

We will be using Access XP 2002 and Windows XP in this class. However, the lesson here should be valid for just about every version of Access.

The database files will be found on the website, accesslearningzone.com/access/223. While you can download the databases off the website, you will benefit the most by doing the examples yourself.

Quiz

Q1. What is a main topic covered in Microsoft Access 223 Advanced Queries Part 4?
A. Creating crosstab queries
B. Importing data from Excel
C. Building simple forms
D. Designing database tables

Q2. What do crosstab queries allow you to do in Access?
A. Display data in spreadsheet-like format with row and column headers
B. Merge multiple tables together permanently
C. Encrypt your Access database
D. Export data to PowerPoint

Q3. Which query wizards are mentioned in this video?
A. Find Duplicates and Find Unmatched Query wizards
B. Update and Delete Query wizards
C. Report Wizard and Form Wizard
D. Export to PDF Wizard

Q4. What is one of the advanced query properties discussed in this lesson?
A. Showing unique values and unique records
B. Calculating primary keys
C. Setting up data macros
D. Automating backup processes

Q5. What benefit is there to learning how to read and write SQL according to the video?
A. It assists you in working with your databases
B. It only helps in web design
C. It is not relevant to Access users
D. It is only useful in Excel

Q6. What does Richard recommend before taking Access 223?
A. Take Access 220, 221, and 222, plus early 100 and 200 level courses
B. Take Excel 101 first
C. No prerequisites are necessary
D. Only experience with Windows is needed

Q7. Which versions of Access and Windows are used in this class?
A. Access XP 2002 and Windows XP
B. Access 2016 and Windows 10
C. Access 97 and Windows 98
D. Access for Mac and MacOS

Q8. According to the introduction, where can you find the database files for the class?
A. On the website accesslearningzone.com access 223
B. In the Access startup folder
C. On a CD included with the class
D. Through email request only

Q9. What is strongly recommended for getting the most from the examples in this class?
A. Follow along and do the examples yourself
B. Only read the transcript
C. Watch without doing any hands-on work
D. Just read the user manual

Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone continues our discussion of advanced queries in Microsoft Access. In this fourth installment of the series, I will introduce you to crosstab queries, which allow you to create queries that resemble spreadsheets, complete with row and column headers and summarized data positioned within the grid. These queries are especially useful for generating reports and analyzing your data in a more visual format.

We will also take a look at the remaining query wizards still to be covered: the Find Duplicates wizard and the Find Unmatched Query wizard. These tools help you identify repeated or missing records, which is essential for maintaining the integrity of your database.

In addition, I will walk you through some of the more advanced query properties. This includes options for displaying only unique values or records, choosing to show the top 10 records, and a variety of other useful features that can help you fine-tune the way your queries return data.

A portion of this lesson will be devoted to SQL statements. I will explain what SQL is, why it is valuable for anyone working with databases to be able to read and write SQL statements, and how understanding SQL can enhance your ability to manipulate and extract information from your data.

Throughout this session, I will also pass along several additional tips and tricks that I have found valuable when working with queries in Access.

Remember, this is the fourth part of the advanced queries series, and it builds directly on material covered in Access 222. I strongly recommend you work through Access 220, 221, and 222, as well as the foundational 100 and early 200 level courses before moving on to this class. These prerequisites will ensure you have the background knowledge necessary to fully grasp the concepts presented here.

For those concerned about software versions, I am using Access XP 2002 and Windows XP in this class, but the techniques and lessons apply to nearly every version of Access, so you should have no trouble following along regardless of your specific setup.

You may download the database files used in this course from accesslearningzone.com. However, to get the most out of this lesson, I encourage you to work through the examples on your own as you follow along.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Creating crosstab queries
Using the Find Duplicates Query Wizard
Using the Find Unmatched Query Wizard
Setting query properties for unique values
Setting query properties for unique records
Limiting queries to the top 10 records
Introduction to SQL statements in Access
Reading and writing basic SQL in Access
Tips and tricks for advanced queries