Free Lessons
Fast Tips
Topic Index
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  

< Previous: Access Expert 17

Next: Access Expert 19 >

Access Expert Level 18

Expert Microsoft Access Tutorial - 1 Hour, 15 Minutes
This Microsoft Access video tutorial picks up where Expert Level 17 left off. In this class we will continue working with Crosstab Queries. We will also learn how to create Find Duplicate Queries and Find Unmatched Queries, and look at many advanced query properties. Topics include:
  - Crosstab Query
  - Parameters & Criteria in Crosstabs
  - Fixed Column Headings
  - Find Duplicates Query
  - Partition Function, Create Tiers
  - Find Unmatched Query
  - Advanced Query Properties
  - Unique Values, Records
  - Cartesian Products

Order Now

If you would like a preview of what's covered in this class,
click here to watch the first and last lessons of this course (free of charge), or scroll down for more information.


Access Expert Level 18
Description: Access Expert Level 18
Versions: This class was recorded with Microsoft Access 2013. The material is valid for Access 2007 up to 2021. Access has not changed that much over the years.
Pre-Requisites: Access Expert Level 17 strongly recommended
Running Time: 1 Hour, 15 Minutes
Cost: $26.99

This class picks up where Expert Level 17 left off. We are continuing our work with Crosstab Queries. We will also learn about Find Duplicates Queries, and Find Unmatched Queries. We'll also take a look at some advanced query properties, and create a query with a Cartesian Product.

Today we will begin by creating a crosstab query to show sales by sales rep by month. We will learn how to create fixed column headings (so we can decide what to display on top of each column). We'll see how criteria and parameters work with crosstab queries, and how you have to declare explicit query parameters in order to get them to work.

crosstab query


Next we'll create another crosstab query showing sales by sales rep by quarter. We'll use the query wizard to create date groupings and a totals column.  We'll also discuss how to use crosstab query data in other queries.



Next we'll learn how to use the PARTITION function to display our products in tiers of $50 increments. How many products do we have in each category in the $50 to $100 price range? This function will help us figure that out.

partition function


Next we will create a Find Duplicates Query. You can use this type of a query to find duplicate information in your tables. For example, how many customers do we have with the same company name?

find duplicates query


We will cover many of the more advanced query properties such as unique values, unique records, output all fields, recordset type, order by, filter by, ODBC timeout, max records, record locks, orientation, subdatasheet, and more.

advanced query properties


Next we will create a Find Unmatched Query. This will help you to find records in Table A that don't have a matching record in Table B. For example, "show me all of the customers who don't have contacts," or "show me all of the products that aren't in a product category."

find unmatched query


Finally we will build something called a Cartesian Product where you have a record in Table A matched up to each record in Table B. This is great for creating team schedules, for example, where Team 1 has to play each of the other teams in the league twice (home and away).

cartesian product


This is the 18th class in the Access Expert series. There's a lot of great material in this class. Learning Crosstab, Find Duplicates, and Find Unmatched Queries will add extra flexibility to your databases. Of course, if you have any questions about whether or not this class is for you, please contact me.


Complete Outline - Access Expert Level 18

00. Intro (6:26)

01. Crosstab Parameters (16:45)
Sales by Sales Rep by Month Crosstab
Fixed Column Headings
Manual Row Headers
Multiple Row Headings OK
Multiple Column Headings NOT OK
Criteria in Crosstab Queries
Date Criteria in the form yyyy-mm
Declaring Explicit Query Parameters
Declare Parameter Even If Form Field

02. Sales Rep by Quarter (06:11)
Query Wizard Date Groupings
Total Row Headings
Count Row Headings
Crosstabs Used With Other Queries

03. Partition Function (7:29)
Tiers of Products
PARTITION() Parameters
Group Products into Price Brackets
Count of Products in Each Tier
Product Category Row Header
Product Tier Column Header
Value is Count of Products in Tier
04. Find Duplicates Query (21:19)
Create Find Duplicates with Wizard
IN() Function Review
SQL HAVING Clause, Brief Discussion Of
Manually Create Find Duplicates Query
Use an Aggregate Query Count > 1
Find Duplicates Multiple Fields
Unique Values, Unique Records
Field List Properties
Query Properties, Field Properties
Output All Fields
Recordset Type Dynaset vs. Snapshot
Prevent Editing of Records in Query
Order By Property
Filter Query Property
Filter On Load, Order By On Load
What is an ODBC Database
ODBC Timeout, Max Records
Record Locks, Brief Introduction
Record Locking
All Records, Edited Records
Orientation Right-to-Left, Left-to-Right
Subdatasheet Height
Subdatasheet Expanded

05. Find Unmatched Query (11:36)
Use Wizard
Create Unmatched Query Manually
Left Outer Join Important
Is Null
Cartesian Product
Cross Product
Softball Schedule
Each Team Plays Each Other Team Once

06. Review (5:23)


Keywords: Crosstab Query, Find Duplicates, Find Unmatched, microsoft access tutorial, microsoft access tutorial, microsoft access training, access 2007, access 2010, access 2013, fixed column headings, query parameters, partition, unique values, unique records, query properties, output all fields, recordset type, dynaset, snapshot, order by, odbc, max records, record locking, orientation, subdatasheet, cartesian product, cross product


The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 7/24/2024 9:36:59 AM. PLT: 0s