By Richard Rost 15 years ago
This is the full text listing of one of our handbooks. There is a lot more to this handbook. The full-color screen shots have been removed for this page. This text is simply provided so that the search engines will index the course contents. This is so any customer searching for a topic can find what class it's covered in. If you are interested in more about information about our courses, click here for our complete course listing. For details on how to purchase a handbook, visit our handbooks page.
Microsoft Access 223
Course Handbook Supplement
By Richard Rost
PO Box 1308, Amherst NY 14226 USA
First Printing 5/5/06
Copyright 2006 by Amicron Computing
All Rights Reserved
Welcome to Microsoft Access 223: Advanced Queries 4.
This handbook is designed to be a supplement to the full 599CD video course for Microsoft Access 223. We recommend you use this handbook to follow along with the class videos. This handbook is not meant as a stand-alone study guide.
We do recommend that you watch the course videos one time through, paying attention to the lessons covered. Follow along with the course videos using this guide. Take notes on the pages where needed. Then, watch the videos a second time, practicing the examples yourself on your computer.
Table of Contents
Table of Contents 2
Lesson 1. Crosstab Queries 4
Lesson 2: Crosstab Queries 11
Lesson 3: Crosstab Query Wizard 19
Lesson 4: Query Wizards 22
Lesson 5: Query Properties 29
Lesson 6: Beginner SQL 35
Lesson 7: Query Tips and Tricks 43
Welcome to Microsoft Access 223: Advanced Queries 4, brought to you by 599CD.com. I am your instructor, Richard Rost.
Objectives for today’s class:
· Crosstab Queries
· Other Query Wizards
· Query Properties
· SQL Statements
The class follows Microsoft Access 222. We’ll be using the same database that we’ve been using since Microsoft Access 101. I strongly recommend that you watch all previous classes before you start with this class.
In this class we will be using Microsoft Access XP (2002) and Windows XP, however this course is valid for any version of Windows, you should have no problems following this course if you are using Access 97, 2000 or 2003.
If you have not been building your database yourself since Access 101, you can get a copy of my database file at the website www.AccessLearningZone.com/Access/223 . If you purchased this course on CD-ROM or downloaded it from our website, you’ll find that it’s been installed in your help folder.
Lesson 1. Crosstab Queries
We’re going to start by learning Crosstab queries. Now Crosstab queries are useful if you want to summarize the data in the rows of your table. Crosstab queries can group and summarize information into a row and column format much like a spreadsheet. We’ve got certain criteria down a row – certain criteria across a column – and you can see the data in the sheet itself. Crosstab queries also kind of like pivot tables.
A Crosstab query is great for a very long list of sales information by date, for example – where each field represents a particular sale. Let’s find the sales log table. Each one of these records represents a particular sale. Now wouldn’t it be nice if I could see the customer information along with the month of the sale? Or let’s say I want to see the sale date going down by month on the rows and the sales rep across the columns – and get a sum for each of those. Well, that’s what we can use a Crosstab query for.
The first thing I want to do is change some of the dates in the SaleDate column so we’ve got some different numbers to work with. So I’m going to highlight all of these rows and copy them (Control+C) and then I’m going to click on the blank row and hit Control+V (paste).
Now I’m just going to change some of these dates and add some February, March, and April sales from this year and from last year. I’m going to also change some of the Customer IDs and a few prices just to mix things up. Just for review, let’s make a simple query to see sales by month.
Let’s close that window and make a new query in Design View. We need our SalesLogT table. We can then bring in our SaleDate, UnitPrice, and Quantity.
From that, we can calculate our own total called OrderTotal:UnitPrice * Quantity. If I run the query at this point, I get the following. But how do I break these down by month?
Well, instead of SaleDate, let’s say we want SaleMonth: Format([SaleDate],”mmm”). Let’s see what that looks like.
Let’s save this as SalesLogByMonthQ and run it. Look at that! It shows all the months, but it’s not giving me the years.
To see the years, all you have to do is change the format of the date as SaleMonth: Format([SaleDate],”yy-mmm”).
When you run it, you can see the year in there. Let’s leave it like this for now.
We can group these together and sum these up back in Design View. Do we need UnitPrice and Quantity to calculate OrderTotal? No – if delete these fields and run the query, everything still works. You don’t always need the fields for a calculated value to work.
Now it’s just a simple matter of grouping the fields. We can group the OrderTotal field by Sum.
We get the following upon running.
That’s a simple “Group By” query that we’ve done before. But what if you want to see this broken down by month and also by state across the columns? That’s where your Crosstab query comes in. Let’s add in the States and see what happens. Let’s go back to Design View and let’s drop in the states. Because we have customer data, we can locate State data. In other words, we can relate the state information to the CustomerID data.
So let’s add in another table and bring in the CustomerT table.
The relationship is there but let’s change the Join type.
We need to change the Join type to Include ALL records from ‘SalesLogT’ and only those records from ‘CustomerT’ where the joined fields are equal.
Let’s run our query and see what happens. Everything looks fine.
So let’s try adding in the State.
When we run the query, it looks just fine. This is what I’m looking for, but - I want the State to go across the top. We need to make a Crosstab query based on this one.
Lesson 2: Crosstab Queries
We’ll start by making a new query based on our SalesLogByMonthQ query. In Design View, bring in your SalesLogByMonthQ query.
On the toolbar, we’ve got a Query Type button. Click it and select the Crosstab Query option.
When you do that, you get a new row named Crosstab:.
The Crosstab field is where we’re going to specify the different pieces of our query. What pieces? Well, we need a column header, a row header, and our data. Grab State and drag it to the Field row. In the Crosstab row, select Column Heading.
Next, bring down the SaleMonth and let’s make that our Row Heading.
Finally, we’ve got our OrderTotal. We can drag that down and make it our Value. We also need to change the “Group By” value in the Total row to Sum so that we can run the query.
When we run the query, we get the following. That’s a Crosstab query! We have sales by month going down the column, and we’ve got the states going across the top.
Can we use parameters to whittle this down a little bit? Yes – let’s take a look. First, let’s save this query as SalesLogCrosstabQ. Open the SalesLogByMonthQ query in Design View and add in SaleDate again – but not show it! Uncheck it to hide it.
Now we need to put some criteria in there. Let’s add Between [start date] And [end date] as the criteria.
When we save it and run it, we can enter in a start date and end date to get the following.
Let’s go back into the SalesLogByMonthQ query design to declare our parameters. We need to tell the query what its parameters are. Right-click above the query rows and select the Parameters option. (Or select Parameters from the Query menu.)
In the Parameters window, enter in your parameters and define their types. Hit OK and save the query.
Now let’s run our Crosstab query. After entering in a start and end date, you will see the following. If you’re making a Crosstab query, you have to declare your parameters.
Can you have multiple rows in your Crosstab query? Yes, you can. Let’s go to the SalesLogByMonthQ query. Instead of lumping the month and year together in the SaleMonth field, let’s change it to just month. Let’s make a new one by copying it and changing it from SaleMonth to SaleYear with a “yyyy” format.
Now if I save and run this, I get the SaleYear and SaleMonth. Save that so we can open our Crosstab query in Design View.
If we add in SaleYear and make it a Row Heading, we get the following when we run it.
What if we wanted the state as the row, and the month and year as the column heading? Let’s try that. Let’s make the State the row heading, get rid of the SaleYear, and make SaleMonth as the column heading.
When we run it, we get our State down the column and our month across the top.
If we were to fill in the cities of these states, we would need to back into the SalesLogByMonthQ design and bring in the City field to get the following.
This is what we get when we add the City field to the Crosstab query and make it a row heading.
Notice that the months are in alphabetical order. I don’t want that. Here’s how you can specify the column headers in a Crosstab query.
Go into the properties for the query itself (right-click or select from the View menu) to bring up the query properties window.
You can specify whatever column headings you want – but, make sure they match up with the column headings that you’re getting when you run the query.
Save it and run it to get the following. Now the months are in chronological order.
Lesson 3: Crosstab Query Wizard
In this short lesson, I’m going to show you how to make the Crosstab Query Wizard. So let’s go to Queries and make a new query but select Crosstab Query Wizard this time instead of Design View.
When the wizard loads, select the SalesLogByMonthQ query.
Hit Next. Then select the field values that you want for row headings. Let’s add SaleYear and SaleMonth.
Hit Next. Then select the field values that you want for column headings. Let’s go with the State.
Hit Next. Then select OrderTotal for the number that you want calculated for each column and row intersection. And let’s use Count for the function.
Hit Next. This will be the SalesLogByMonthQ_Crosstab, so hit Finish.
Enter in a start date and end date for your parameters to get the following.
Lesson 4: Query Wizards
In this lesson, we’re going to take a look at some of the other query wizards: Simple, Find Duplicates, and Find Unmatched. The Find Duplicates wizard is good for finding duplicate records, and the Find Unmatched wizard searches for records that have no match in a table.
Let’s click on Queries and then New. Select the Simple Query Wizard. Select the CustomerT table to base this query on and add the CustomerID, FirstName, LastName, and CompanyName fields.
Hit Next. We’ll use CustomerT Query as the title and we’ll hit Finish.
And this is what you’ll get which is great if you know nothing about queries.
Let’s close that and make a new query with the Find Duplicates Wizard. Let’s say we want to find customers with duplicate company names. Hit OK.
The wizard loads up and asks which table we want to search. Let’s search the CustomerT table.
Click Next. Then select the fields that might contain duplicate information: CompanyName.
Do we want to see additional information? Yes, let’s go ahead and add in the CustomerID, FirstName, LastName, City, and State fields.
Hit Next. We’ll name this query FindDuplicatesForCustomerTQ and hit Finish.
And there they are – duplicate company names!
Let’s look at the Find Unmatched Query Wizard. Let’s say I want to see all the customers that have no contacts.
In the wizard window, let’s select our CustomerT table again and hit Next. On the next screen, select the ContactT table for the table that contains the related records.
Hit Next. Now we need to select the related information in both table: CustomerID.
Hit Next. Then select the fields that you want to see in the results: CustomerID, FirstName, LastName, CompanyName, and Phone.
Click Next. We can name this query FindUnmatchedCustomerContactsQ and click Finish.
Here are all the customers that have no contacts.
If you want to verify this, we can take a look at the ContactT table to see that we have very few contacts in listed.
Lesson 5: Query Properties
In this lesson, we’re going to learn about different query properties including Top Values, Unique Records, Unique Values, and Output All Fields.
So let’s open up a new query in Design View. In this new query, we’re going to pull in the SalesLogT table that we have. Now I want to generate a list of all my top sales, so let’s drop in SalesLogID, UnitPrice, and the SaleDate. To see these sorted, let’s sort UnitPrice descending.
If I run the query, I get the following. As you can see, I have 14 records. But let’s say I only want to see the top 5. How do I do that?
Well let’s go back in Design View and pull up the query’s properties.
We want to look for the Top Values property. When you click in the field and drop it down, you can see a list of different values. Let’s type in 7.
When I run the query, I can see the top 7 records.
Now if we sort this ascending and run it, we’ll see the bottom 7 records.
You can also put a percentage in there. We can put in a 5% Top Value in properties.
Sort the list descending and run the query to get the following.
The next properties that I’d like to talk about are Unique Values and Unique Records. Unique Values will show you unique values for the fields that you have in your query whereas Unique Records will show you unique values for an entire record.
Let’s close the SalesLogT query and create a new one in Design View. And let’s base this one on the customer table. Bring in the CompanyName all by itself.
Now if I run this query, you can see that I’ve got some duplicates.
I’m going to come into the query’s properties and change Unique Values from No to Yes.
When I run the query, the duplicates are gone.
Now, I’m going to come into the query’s properties and change Unique Records from No to Yes.
Upon running the query, you’re going to see everything because we don’t have any duplicate records. If we had identical orders including the CustomerID, then we would see those records flagged as duplicated.
Watch when I pull in FirstName and LastName for example, set Unique Values to Yes, and run the query. You can see that records with the same CompanyName have different people in them. If let’s say “XYZ Corp” had “Barbara Peters” as both contact records, then say “XYZ Corp” would show up only once.
Lesson 6: Beginner SQL
In this lesson, we’re going to learn a little bit about SQL, what it is, why we need to use it, and some basic SQL statements. SQL stands for Structured Query Language. In a nutshell, an SQL statement looks like this: SELECT field FROM table.
Let’s open up our ContactQ query in Design View.
I can see the SQL statement behind this and selecting SQL View from the View button on the toolbar.
And there’s it is!
For now, let’s create a new query in Design View and keep it basic. We’ll pull in just our customer table (CustomerT) and add in just the CompanyName.
Now, what did Access create for our SQL statement? SELECT CompanyName FROM CustomerT.
If I run that, I get the following.
Design View is just a graphical representation of SQL statements. How do I add a second field in SQL View? Well I can just add one behind the CompanyName.
When I run it, I get the following.
If I were to switch the fields around, Access will edit the SQL statement.
Let’s take a look at what a multiple table query looks like. Let’s go back to Design View and add in our ContactT table. Let’s say I want to see the contact Notes so drop that in.
This is what it looks like when I run it.
Let’s take a look at it under the SQL View.
Let’s return to Design View and get rid of the CustomerID and Contact table. Let’s bring in a State field. I want to show you something with some criteria in it. Let’s say I want the state to be New York.
Now let’s see what the SQL View looks like.
If I run it, I get the following.
Remember way back, we made a ContactF form and that we could pick a customer from that form?
If I open up the properties for that customer combo box in Design View, we have a Row Source that has a SELECT statement in it.
Shif+F2 to zoom in on that section. If you know SQL, you can modify this row source. The combo box in this query does the sorting for us, but how would I do that manually?
How can I add sorting to this? Let’s return to the Design View of our example query and sort the CompanyName ascending and see what that looks like in SQL View.
In SQL View, Access adds an ORDER BY clause at the end. And if you want the sort to descend, add DESC.
The one thing that I like to use SQL for is making a Union Query. A Union Query takes the records from two tables and joins them together to make it look like one big table. For example, let’s say you want to send out greeting cards. You’ve got your vendors in one table, your customers in a second table, and your employees in a third table. And you want to send all of them greeting cards for the holidays.
Let’s clear the fields on our sample query and bring in FirstName, LastName, and Address.
Now let’s go into SQL View. I’m going to copy the information already in there and paste it down below. But replace CustomerT with EmployeeT in the information that you pasted.
To join these two together, we need to type in UNION.
When we run it, we get the following.
Notice, I can’t go back to Design View. That’s because this is a Union Query. But you can save it as UnionQueryExampleQ.
That’s the benefit of using Union queries – to be able to pull stuff together from multiple tables. And you’ve got to now SQL in order to do it.
Lesson 7: Query Tips and Tricks
The first thing that I would like to talk about is the IN() function. The IN() function is useful if you want to include a list of items that you want your queries to trigger on a criteria. For example, let’s say I want a list of companies only from New York, California, or Pennsylvania.
Well, I’ll create a new query in Design View and bring in the CustomerT table. I’ll add the CompanyName and State fields. For the criteria, I’m going to use the IN() function.
I’ll run it and there they are.
The Cartesian Product is basically a query of two tables without a join line. Using our sample query, we can get rid of the State field, but let’s bring in the FirstName, LastName, and Phone.
We’re going to add in a new table: ContactT but let’s delete the join line and add in the DateTime and Notes.
When we run the query (saved as CartesianQ), we get 66 records with a bunch of repeating notes. The Cartesian Product is showing the contact record for each customer record. Whether they’re related or not doesn’t matter. This might be handy if you want a list of every employee and a list of tests. The Cartesian Product will show every test for every employee.
If you want to see a list of records in a table or query, you can use the Count(*) function, like if you want to see how many customers you have for example. Create a new query in Design View and drop in the CustomerT table. But before you bring in any fields, type in Count(*).
When you run it, you can see how many customers you have.
Let’s say you want to count the customers only from New York. Well just bring in the State, uncheck the box, and set its criteria to New York.
When you run it, you’ll see how many customers you have from New York.
Let’s get rid of the CountCust: Count(*) and State field, and bring in CustomerSince and CompanyName.
If you want, you can right-click to bring up the field properties and change the Format to Medium Date.
When you run it, it’ll look like this.
Or, you can format the field by entering the following.
When you run it, it’ll look like this.
Let’s take a moment now to review what we covered in class.
· We learned about Crosstab Queries, how we can have column headers and row headers, and we built them by ourselves.
· We built a Crosstab Query using a Crosstab Wizard.
· We learned about the other wizards: the Simple Wizard, the Find Duplicated Query, and the Find Unmatched Records Query.
· We learned about our query properties: Top Ten Records, Unique Values, Unique Records.
· We learned some basic SQL, SQL statements, Union Queries – and how to manually edit the record source of combo boxes.
· We learned some other tricks and tips.
Tell us what you think. Log on to www.AccessLearningZone.com/Survey and take a short survey about this course.
RICK’S NOTE: I really do enjoy getting surveys from you! Make sure you visit the web page above and fill out the survey for this class. Let me know if I’ve moved too fast, and whether or not I covered material that was helpful to you!
Take your skills check quiz at www.AccessLearningZone.com/Test. If you pass, you can print out a Certificate of Completion.
What’s next? Visit www.AccessLearningZone.com for our complete list of Microsoft Access courses.
Need Help? Visit www.AccessLearningZone.com/TechHelp for Microsoft assistance.
Make sure you’re on our Mailing List. Go to www.AccessLearningZone.com/Mailing for details.
What’s New? Visit www.AccessLearningZone.com/WhatsNew for a list of what’s been added..
Contact Us. If you have any questions, go to www.AccessLearningZone.com/Contact for information on how you can contact us by phone, email, or live online chat.
Don’t forget to visit our Microsoft Access Forum online at: www.AccessLearningZone.com/Forums. You can chat with our instructors, other users, and even Richard too. You can ask us all of your Access questions, get answers, and tell us what you thought of our class.
This course, handbook, videos, and other materials are copyright 2002-06 by Amicron Computing. All rights reserved. No portion of this course, handbook, videos, or other course materials may be reproduced, copied, edited, or otherwise distributed without the express written permission of Amicron Computing. Amicron Computing shall not be held liable for any errors or omissions in this document.
This document may not be used as part of a training course without express, written permission from Amicron Computing and the purchase of an Instructional License. For details, contact:
PO Box 1308
Amherst NY 14226 USA
You may want to read these articles from the 599CD News: