Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
 
 
 
Courses - Microsoft Access 223
Description: Advanced Access Queries, Part 4
Running Time: 80 minutes
Pre-Requisites: Access 222 very strongly recommended
Previous Lesson: Access 222
Next Lesson: Access 301
Main Topics: Crosstab Query, Parameters, Find Duplicates, Find Unmatched, Top X, Basic SQL
Versions: This course is valid for Access 2000 through 2003. If you are using Access 2007 or 2010, you will still benefit from this course. You will find that the concepts are the same, but there are cosmetic differences between the versions.

    
 
Microsoft Access 223
Advanced Access Queries 4

Crosstab Queries, Query Parameters, Find Duplicates, Unmatched, Query Properties, Top X Records, SQL Primer, More. 80 Minutes.
 

AC223 Major Topics

  • Crosstab Queries
  • Declared Query Parameters
  • Find Duplicates
  • Find Unmatched
  • Query Properties
  • Top X Records
  • Basic SQL

This is the fourth (and final) course in our mini series on Advanced Query Development. This class focuses on additional topics for making your Queries powerful.

We'll begin by looking at Crosstab Queries which allow you to create spreadsheet-like views (almost like Pivot Tables) in your database. This allows you to, say, take a whole list of sales and summarize them by month and by state.

I'll show you how to do this both by scratch, and with the Crosstab Query Wizard (not an evil wizard). Or how about putting the state across the column header...

 

Oh, and have you ever used the Query Parameters window to declare your parameters? Didn't think so. I'll show you what it is... and why you sometimes need to use it.


 

Next, we'll learn how to use the Find Duplicates and Find Unmatched query wizards. The first will allow us to find duplicated records (like all records where the company name is the same). The second will allow us to find customers without contacts (unmatched records in a related table). This is really cool if you want to send a letter to any customers, for example, who have no orders!

 

Next we'll learn about some Query Properties, such as how to see the Top X Values of your query results (maybe you only want to see the top 10 performing sales reps - instead of all 500).

 

We'll also learn a lot of the other properties, like Unique Values, Unique Records, Column Headers, Output All Fields, etc.

Next, the BIG lesson... SQL (Structured Query Language). Lots of people have been asking to learn about it... so now we're going to start covering it. Here's a look...

 

OK, didn't mean to scare you. That was just a preview. I'll explain what it all means in class. It's really quite easy to learn. Here's a much simpler one...

 

This statement (above) just gives you a listing of all of your company names from the customer table. SQL is really easy to learn - and it's powerful. If you know how to use it... there are a lot of places in your Access database where it's handy to know SQL. Again - it's just one more thing to make your databases shine.

That's not the end of class, however. I've got a lot of little tips and tricks that I've also thrown in at the end of this one (stuff that didn't fit in any other Query class). If you like queries, you'll love this class.

 

Access 223 Outline
 

1. Crosstab Queries, Part 1
What is a Crosstab Query?
Similar to an Excel PivotTable
Format() Function "yy-mmm"
Sales Totals by Month
Sales Totals by State by Month

2. Crosstab Queries, Part 2
Create a Crosstab Query
Column Heading
Row Heading
Value
Adding Date Criteria
Defined Query Parameters
Breaking Down by Year
Specify Column Headings

3. Crosstab Query Wizard
Using the Wizard to Build a Crosstab Query

4. Other Query Wizards
Simple Query Wizard
Find Duplicates Query Wizard
Find Unmatched Query Wizard
Finding duplicate customer records
Find customers that have no contacts

5. Query Properties
Showing the Top X Values (eg Top 10)
Showing the Top X% Values (eg Top 10%)
Unique Values
Unique Records

6. Beginner SQL
SELECT field FROM table
Build a Query in the Designer
Switching to SQL View
Multiple Fields
Removing Clutter that Access Adds to SQL
INNER JOIN
WHERE clause
Placing SQL statements in combo box Row Source property
ORDER BY clause
ORDER BY DESC
UNION Query - can only be done with SQL

7. Query Tips & Tricks
IN() Function
Cartesian Product
Count(*) Function
Medium Date Format
 

 

 


Try a FREE Demo Lesson

 
 
 

 

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

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

LinkedIn
Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 3/28/2024 8:01:07 AM. PLT: 0s