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

    

Order before 1/20/2017 to get a FREE upgrade to our Access 2010 version!
Click here for details

 
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

 
 
 

Student Interaction: Microsoft Access 223

Richard on 1/1/2007:  Crosstab and Union Queries, Query Wizards, Advanced Query Properties and Functions, SQL Primer, More.
 lehu on 2/26/2008: what is the procedure for deduping table against another table?
Richard Rost on 2/28/2008: Use a FIND-DUPLICATES query. We learn about them in Access 223, lesson 4. If you then need to go back and delete some of those duplicate values, use a DELETE query from 222.
Walter Pohle on 5/16/2008: If you have missing data like address field can you put in IsNull in Criteria

Richard Rost on 5/24/2008: Walter, yes... but make sure you type it in as two words: "Is Null" not "IsNull". The latter is a function, like IsNull(FirstName), whereas in a Query paramter, you would type in "Is Null". Confusing, I know.
joel torres on 9/18/2008: what is the procedure when you checked the checkbox a message will appear on the textbox on the form view?
Richard Rost on 9/19/2008: Joel, I'm not sure I understand your question. You want to click on a checkbox and change the value of a textbox? You can do that with an AfterUpdate event which I cover in Access 207 (as a macro) and Access 303 (as an Event Procedure).
 Elsa on 11/11/2008: Hi Richard,
I played around with the security settings on my access database and now I locked myself out of my own database. I can access the database from my colleague’s computer (without being prompted logon information) but not from my own. Access prompts a logon message box on my computer but my password is being denied. Do you discuss security options in future tutorials and if not, will you please help me with this issue?

Richard Rost on 11/14/2008: Elsa, I have not covered Access User-Level Security in any of my tutorials as of yet (see my recent Blog post for reasons why). Depending on how "tight" you secured your database, there are a couple of things you can try. The easiest is just to create a new Workgroup file, or attach to the default System MDW file. If you didn't lock down all of your objects, that might work. You can also try importing your objects into a new blank database. If this doesn't help, contact me via the TechHelp page and I'll try to assist you further.
Elsa on 11/14/2008: Thank you Richard,
I renamed the system.mdw file and that allowed me back into my database. For now I will keep away from any security options.
Regards
Elsa

Mimi Bailey on 11/26/2008: Richard, Is it possible to build a crosstab query which would have more than 3 row headers. The wizard constrains us to 3. Is there a work around for this?
TBone on 11/1/2009: Is a Cartesian Product the same as Many-To-Many?
Richard Rost on 11/2/2009: No, a cartesian product is every record from table 1 paired up with every record from table 2. A many-to-many relationship is SPECIFIC records from table 1 joined to records from table 2 using a relationship specified in table 3.
Alex Hedley on 5/6/2010: Hi :)
Is there a way to make the Crosstab Query show in a Form or made into a Report or is it easier just to use Pivot Tables?
Alex

Reply from Richard Rost:

Since the column headers will change in a crosstab query, you're better off using a PivotTable or PivotChart... unless you're SURE they're not going to change, then sure - build a form based on your crosstab query.

 

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