Access 2007-2013
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
NEW Release: Access Advanced Level 2    dismiss
 
 

< 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: Recorded with Access 2013. Also use with 2007 and 2010. Access 2003 users should get Access 223.
Pre-Requisites: Access Expert Level 17 strongly recommended
Running Time: 1 Hour, 15 Minutes
Cost: $24.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.

crosstab

 

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
 
 

Student Interaction: Microsoft Access Expert 18

Richard on 1/25/2014:  Microsoft Access Expert Level 18 is 1 hour, 15 minutes long. In this class 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. 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 Click here for more information on Access Expert Level 18, including a course outline, sample videos, and more. This course was recorded using Access 2013, but is also valid for Access 2007 and 2010 users. This class follows Expert Level 17. This next class in the series is Expert Level 19.
Tracy Williams on 1/28/2014: How much Access do you need to know if you want to learn SQL?

Reply from Richard Rost:

You can start learning SQL any time, but I'd recommend finishing my Beginner series, and at least Expert 1 and 2 before starting my SQL SEMINAR.

Larisa on 1/31/2014: Hello-
Are you still working in email session?

Reply from Richard Rost:

Yes, it's still in the works. I've been unexpectedly busy these past few weeks but I hope to have this out soon.

william samson on 2/20/2014: thumb up
Hamish Isaacs on 2/26/2014: Hi Richard

I'm extremely excited about this seminar. Looking forward to when this is released.

Regards

Hamish

Keith Williamson on 3/6/2014: Hi Richard

You have mentioned this topic on a number of occasions and it is something which I desperately need. Are you likely to be completing it any time soon?

Kind Regards

keith

Reply from Richard Rost:

Yes. Very, VERY soon. I'm finishing up Access Expert 19 today and then the Email Seminar is next on my list.

faye p on 3/20/2014: I think I posted on the wrong board. Richard what access tutorial covers pivot tables...I do not see it in 2013 so what replaces it or what?


Reply from Richard Rost:

Pivot Tables are GONE from Access 2013. Microsoft wants you to use Excel for this. You can get similar results using a Crosstab Query, but it's nowhere near as powerful as Excel.

 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP