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.

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.

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?

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.

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."

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).

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) |

|