Introduction

Welcome! Crosstabs, Duplicates, Unmatched

S M L XL FS | Slo Reg Fast 2x | Bookmark

Welcome to Microsoft Access Expert Level 18. In this course we will focus on advanced query topics including crosstab queries, working with criteria and parameters, fixed column headings, date groupings, and the Partition function to create ranges or tiers. We will discuss finding duplicate records, finding unmatched records between tables, and cover advanced query properties such as Unique Values, Unique Records, record locking, recordset types, and Cartesian products. This course builds on Expert Level 17 and is designed for those ready to explore more complex features in Microsoft Access queries.

Navigation

Prev: X18
Next: Lesson 01

Keywords

Access Expert, crosstab queries, crosstab query parameters, fixed column headings, date groupings, Partition function, product tiers, Find Duplicates query, Find Unmatched query, Unique Values, Unique Records, record locking, recordset types, Cartesian pr

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Microsoft Access Expert Level 18, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

We will spend most of today's class talking about crosstab queries. We introduced crosstab queries in Expert 17. Today we will go into crosstab queries in a lot more depth.

We will see a few more examples of crosstab queries. We will learn how crosstab queries work with criteria and parameters. We will set up fixed column headings, date groupings, and we will learn how to set up the Partition function to create tiers or ranges of records.

We will learn about the Find Duplicates query used to find duplicate information in your tables. We will learn how to find unmatched records in your database. In other words, a value from Table A that does not have a matching value in Table B. For example, all of the customers who do not have a matching contact. And we will talk about a lot more of the advanced query properties.

We will learn about the Unique Values, Unique Records properties, record locking, recordset types, Cartesian products, and lots more.

This class follows Access Expert Level 17. In Level 17, we wrapped up our chapter on action queries and we introduced crosstab queries. So if you missed Access Expert Level 17, I recommend you take that class before watching this one.

Today's class is recorded with Access 2013. It should work just fine with 2007 and 2010. If you are using Access 2003 or earlier, go to my website and download Access 223. That is the class that talks about crosstab queries.

My courses are broken up into Beginner, Expert, Advanced, and Developer Level classes. Beginner level classes are for novices. You should understand all the topics covered in them by the time you get to the Expert Level classes, which you are in now.

When you finish all of the Expert Level classes, the Advanced classes will cover event programming and macros, and the Developer classes will cover Visual Basic for Applications.

Each group of classes is broken down into multiple levels, Level 1, 2, 3, and so on.

In addition to my normal Access classes, I also have seminars designed to teach specific topics. My seminars include building web-based databases, creating forms and reports that look like calendars, securing your database, working with images and attachments, writing work orders and running a service business, tracking accounts payable, learning the SQL programming language, creating loan amortization schedules, and lots more.

You can find details on all of these seminars and more on my website at AccessLearningZone.com.

If you have questions about the topics covered in today's lessons, please feel free to post them in my student forums.

If you are watching this course in the online theater on my website, you should see the student forum for each lesson appear in a small window next to the class video. Here you will see all of the questions that other students have asked as well as my responses to them and comments that other students have made.

I encourage you to read through these questions and answers as you start each lesson and feel free to join in the discussion. If you are not watching these lessons on my website, you can still visit the student forums later by visiting AccessLearningZone.com/forums.

To get the most out of this course, I recommend you sit back, relax, and watch each lesson completely through once without trying to do anything on your computer. Then, replay the lesson from the beginning and follow along with my examples. Actually, create the same database that I make in the video, step by step. Do not try to apply what you are learning right now to other projects until you have mastered the sample database from class.

If you get stuck or do not understand something, watch the video again from the beginning or tell me what is wrong in the student forum and I will do my best to help you.

Most importantly, keep an open mind. Access may seem intimidating at first, but once you get the hang of it, you will see that it is real easy to use.

Now, I strongly encourage you to build the database that I build in today's class by following along with the videos. However, if you would like to download a sample copy of my finished database file, you can find it on my website at AccessLearningZone.com/databases.

Sometimes, if you get stuck, the easiest way to learn is to tear apart someone else's database. One of the ways that I taught myself Access years ago was by tearing apart the Northwind Traders database that comes up in Microsoft Access. You will find there is a sample database for each of my courses on my website.

Now let's take a few minutes and go over exactly what we are going to cover in today's class.

In lesson one, we are continuing our work with crosstab queries from the last class. We will begin with crosstab query parameters and fixed column headings.

In lesson two, we will take a look at a similar example, sales by quarter by sales rep, where the quarter is the column header.

In lesson three, we are going to use the Partition function to create product tiers based on the unit price of each product. Then we will create a crosstab query to show the count of the number of products in each tier.

In lesson four, we are going to learn how to create a Find Duplicates query, where we can find duplicate values in a table, and we are going to go over some of the more advanced query properties.

In lesson five, we are going to learn how to build Find Unmatched queries and Cartesian products.

Quiz

Q1. What is the main topic of Access Expert Level 18?
A. Advanced crosstab queries
B. Database security
C. Creating macros
D. SQL programming

Q2. What function is used to create tiers or ranges of records in a crosstab query?
A. Tier function
B. Partition function
C. Range function
D. Segment function

Q3. What is a Find Duplicates query used for?
A. To locate duplicate values in a table
B. To combine two tables into one
C. To import data from Excel
D. To delete duplicate tables

Q4. If you want to find customers who do NOT have a matching contact in another table, which type of query would you use?
A. Crosstab query
B. Find Duplicates query
C. Find Unmatched query
D. Update query

Q5. Which property should be adjusted if you want to ensure only unique rows are shown in a query's results?
A. Unique Values or Unique Records
B. Show Totals
C. Enable Macros
D. Default View

Q6. What does the term "Cartesian product" refer to in Microsoft Access?
A. The set of all possible combinations of rows from two tables
B. A list of duplicate records
C. A filtered list of customer names
D. A sorted list of product categories

Q7. When learning from Richard Rost's videos, what is recommended before trying to use the techniques in your own projects?
A. Watch each lesson and immediately apply it to your project
B. Watch and build the sample database from class first
C. Skip to the end of the lesson to see the final result
D. Only read the student forums

Q8. What is the purpose of setting fixed column headings in a crosstab query?
A. To prevent columns from moving
B. To specify which columns should appear, regardless of the data
C. To highlight the highest values only
D. To sort columns alphabetically

Q9. Which function groups dates automatically in crosstab queries?
A. DateGroup
B. MonthName
C. DatePart
D. DateTier

Q10. Where can you find sample copies of databases built in the lessons?
A. On AccessLearningZone.com/databases
B. On Microsoft's official website
C. Only by emailing the instructor
D. In the Access help file

Q11. What is one way Richard Rost suggests resolving confusion or trouble with a lesson?
A. Rewatch the video or ask questions in the student forum
B. Skip the lesson and move to the next one
C. Reset your computer
D. Purchase a different course

Q12. What is the suggested order for taking Richard Rost's Access classes?
A. Developer, Advanced, Expert, Beginner
B. Advanced, Developer, Expert, Beginner
C. Beginner, Expert, Advanced, Developer
D. Expert, Beginner, Advanced, Developer

Q13. What is one topic that will be covered in the Advanced level classes?
A. Event programming and macros
B. Creating simple tables
C. Access installation
D. Database backup

Q14. What should you do before joining discussions in the forums?
A. Read through existing questions and answers
B. Email all your questions directly to the instructor
C. Ignore other students' questions
D. Log in with a special admin account

Answers: 1-A; 2-B; 3-A; 4-C; 5-A; 6-A; 7-B; 8-B; 9-C; 10-A; 11-A; 12-C; 13-A; 14-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 is Expert Level 18, where we are going to take a closer look at crosstab queries. In the previous class, Expert Level 17, we started working with crosstab queries. This time, we're taking those concepts much further.

We'll look at several more examples of crosstab queries and discuss how they function when you add criteria and parameters. I'll show you how to set up fixed column headings and group your data by dates. You'll also learn to use the Partition function to break your records into tiers or ranges, making your analysis much more flexible.

In addition, you'll learn how to use the Find Duplicates query to track down redundant information in your tables. We will go through the process of locating unmatched records in your database, like figuring out which customers do not have a corresponding contact record. We'll also cover a range of advanced query properties including Unique Values, Unique Records, record locking, different recordset types, Cartesian products, and several other important features.

If you missed Expert Level 17, I recommend starting there, since we finished discussing action queries and introduced crosstab queries in that course. I am using Access 2013 in these demonstrations, but if you have Access 2007 or 2010, everything here will work for you. Those who use Access 2003 or an earlier version should check my Access 223 course on my website for information on crosstab queries suited to your software.

My curriculum is structured into Beginner, Expert, Advanced, and Developer levels. The Beginner classes are designed for novices, and by the time you reach the Expert level, you'll be comfortable with the foundational material. After you finish the Expert lessons, the Advanced courses introduce event programming and macros, and the Developer level goes into detail on Visual Basic for Applications.

Each of these tracks is divided into individual levels, such as Level 1, Level 2, Level 3, and so forth, making it easier to find exactly where you are in the learning path.

Aside from the regular classes, I offer detailed seminars on specific topics, including web database creation, designing calendar-style forms and reports, database security, working with images and attachments, writing work orders for service businesses, accounts payable tracking, SQL programming, loan amortization schedules, and much more. You can find full details about all these seminars on AccessLearningZone.com.

If you have questions about the material in today's class, I encourage you to visit my student forums. If you are following along with this course on my website, you'll notice each lesson has its own student forum right next to the video, where you can see questions and comments from other students as well as my answers. It's a great way to deepen your understanding and participate in the conversation. If you are not viewing the course on my website, you can still visit the forums later at AccessLearningZone.com/forums.

To make the most of this course, my advice is to watch each lesson all the way through once without trying to follow along or do anything in Access. After that, go back to the beginning and work through it step by step, building the sample database as I do in class. Do not try to use these techniques with your own projects until you are comfortable with the material from the sample file. If you run into problems, review the video again or ask for help in the student forum.

Remember to keep an open mind. Access can seem overwhelming at the beginning, but with some practice, you'll find it's not as complex as it appears.

Although I urge you to build the database along with me as shown in class, you can also download the finished database file from my website at AccessLearningZone.com/databases. Sometimes taking apart a completed database is helpful if you get stuck. This is similar to how I learned Access years ago with the Northwind Traders sample database. There is a downloadable sample file for every course on my site.

Now, here is an overview of what we will be covering:

Lesson one continues our work from the last class, focusing on crosstab query parameters and how to use fixed column headings.

In lesson two, we'll build a query to show sales by quarter and by sales rep, with quarters arranged as column headers.

Lesson three introduces the Partition function, which allows us to create product tiers based on unit prices, and then we will analyze how many products are in each tier using a crosstab query.

Lesson four explores how to use the Find Duplicates query to locate repeated values in your tables and covers some of the more advanced query properties.

In lesson five, we'll see how to build Find Unmatched queries and work with Cartesian products.

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

Crosstab queries in depth
Crosstab query criteria and parameters
Setting fixed column headings in crosstab queries
Date groupings in crosstab queries
Using the Partition function for data tiers
Creating product tiers with Partition
Counting products in each tier with crosstab
Find Duplicates queries
Finding duplicate records in tables
Advanced query properties
Unique Values and Unique Records properties
Record locking and recordset types
Find Unmatched queries
Creating Cartesian products in queries

Article

Welcome to this guide on advanced Microsoft Access query techniques. In this lesson, we will take a deep dive into crosstab queries and some other powerful features you can use to analyze your data efficiently. If you are new to crosstab queries, I recommend having a basic understanding of simple and action queries first.

Crosstab queries in Access allow you to summarize and rearrange data dynamically. Instead of just listing records, a crosstab query lets you group values by two fields at once: one as row headings and one as column headings, similar to a pivot table in Excel. For example, suppose you want to see the total sales for each sales representative broken down by the quarter of the year. A crosstab query makes this easy by displaying sales reps on the left and quarters as column headers, with totals filled in where they intersect.

Let's start by creating a basic crosstab query. Open your database and go to the Create tab, then select Query Design. Add the table that contains your sales data. In Design view, change the query type to Crosstab. Now you will see a new Crosstab row appear in the design grid. For example, suppose you have a table named Sales, with fields like SaleDate, SalesRep, and Amount. Add SalesRep to the field list and set the Crosstab row to Row Heading. Next, add a calculated field to extract the quarter from SaleDate, for example: Expr1: DatePart("q", [SaleDate]). Set this field as the Column Heading. Then, add the Amount field, set it to Value in the Crosstab row, and set the Total row to Sum if you want to total the sales amounts.

Sometimes, you might want to fix the column headings so that all four quarters are always displayed, even if there is no data for a particular quarter. To do this, open the query in SQL View and modify the TRANSFORM statement. For example:

TRANSFORM Sum([Amount]) AS TotalSales
SELECT [SalesRep]
FROM Sales
GROUP BY [SalesRep]
PIVOT DatePart("q",[SaleDate]) IN (1,2,3,4);

This will force the query to always show columns for quarters 1 through 4.

You can also use criteria in a crosstab query to limit the records. For instance, if you only want to see sales for a specific year, add a field for the year, such as: Year: Year([SaleDate]). Set the Criteria row under this field to 2024, for example, and leave the Crosstab row blank for this field. If you want to prompt the user for a year each time the query runs, use a parameter like: [Enter Year:] in the Criteria row.

Date groupings are especially useful. Instead of showing sales by quarter, you can show them by month or week. Just adjust the calculation in the column heading field. For example:

Expr1: Format([SaleDate], "yyyy-mm")

This will group your data by each month in the format 2024-06, 2024-07, and so on.

The Partition function is another advanced tool. You can use it to break your data into ranges, such as price tiers. Suppose you have a Products table with a UnitPrice field. You might want to see how many products fall into certain price ranges, like $0-$9.99, $10-$19.99, and so on. Add a calculated field in your query like this:

Tier: Partition([UnitPrice], 0, 100, 10)

This divides your products into ranges from 0 to 100 in increments of 10. When you use this in a crosstab query, you can show, for example, the count of products in each tier:

TRANSFORM Count([ProductID]) AS ProductCount
SELECT "All Products" AS Description
FROM Products
GROUP BY "All Products"
PIVOT Partition([UnitPrice], 0, 100, 10);

Further, Access provides query wizards for finding duplicate and unmatched records. The Find Duplicates query helps you locate records in a table that have the same value in one or more fields. For example, you might find customers with the same email address or products with the same name. To create this, go to the Create tab, select Query Wizard, and choose Find Duplicates Query Wizard. Follow the prompts to select your table and fields to compare. The resulting query highlights any duplicate values.

The Find Unmatched query finds records in one table that do not have related records in another table. For instance, you could find customers who have not placed any orders. Once again, use the Query Wizard, but this time select the Find Unmatched Query Wizard. Choose the first table (such as Customers) and then the related table (such as Orders), specify the matching fields (like CustomerID), and Access will build a query listing all unmatched records.

When working with advanced query properties, it is helpful to understand a few terms:

- Unique Values: This property, when set to Yes, ensures that only unique combinations of fields are returned in the results. In the query's property sheet, set Unique Values to Yes to filter out duplicate rows.

- Unique Records: This works similarly, but affects records where every field in the output is unique.

- Record Locking: This refers to how Access handles multiple users editing records at the same time. For most read-only queries, this is not an issue, but for update queries, you may want to control whether records are locked while being edited.

- Recordset Types: Access queries can return different types of recordsets, such as Dynaset or Snapshot. Generally, you will use the default setting unless you are programming in VBA and need a specific type for editing or speed.

- Cartesian Product: This occurs when you create a select query from two tables without a join condition. The result includes every possible combination of records from both tables, which is often not what you want so be careful. For example, if you have a table with 10 products and another with 5 categories, a Cartesian query would return 50 rows (10 x 5), showing all combinations.

These advanced query techniques allow you to analyze your data in many ways, summarizing, grouping, and filtering as needed. If you want to practice, create a sample database and try building each of these queries step by step. Experiment with crosstab parameters, different criteria, and try using the Partition function on real data.

If you find yourself stuck or if something does not make sense, review your query setup, double-check your field names, and try rebuilding the query step by step. Practice is key. The more you work with Access queries, the more comfortable you will become with their power and flexibility.

Remember, Microsoft Access is a tool that becomes much easier once you get some hands-on experience. Start with sample data, such as the Northwind database that comes with Access, and explore how you can apply these techniques to your own data analysis needs. If you need more examples, you can find many resources online or in dedicated Access learning communities.

By mastering these advanced query features, you will be able to extract valuable insights from your databases and create dynamic, interactive reports for your users.