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