By Richard Rost 14 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 220
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 220: Advanced Queries 1.
This handbook is designed to be a supplement to the full 599CD video course for Microsoft Access 220. 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. Setting up a Sales Log Table 4
Lesson 2: Sales Log Query 9
Lesson 3: Sales Log Form 15
Lesson 4: Sales Log Form 2 22
Lesson 5: Join Types 27
Lesson 6: Global Relationships, Referential Integrity, and Cascade Delete 35
Welcome to Microsoft Access 220: Advanced Queries 1, brought to you by 599CD.com. I am your instructor, Richard Rost.
Objectives for today’s class:
· Mathematical Calculations in Queries
· Functions in Queries
· Join Types (Inner, Outer)
· Calculated Totals in Form Footers
· Referential Integrity / Cascade Deletes
The class follows Microsoft Access 207. 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/220 . 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. Setting up a Sales Log Table
In this lesson, we’re going to be setting up a Sales Log Table which is going to give us a lot of data to work with for our advanced queries. Here we are inside of our PC Resale sample database. In this class, we’re going to keep track of all the sales that our business makes. Basically, we just enter each item as it’s purchased and this will create a real simple log of everything that our company sells.
Let’s go to our database window.
Go to Tables and click on New to create a new table. Click on Design View and then hit OK.
We’re going to create a Sales Log table. So let’s create a Sales Log ID as our Autonumber field (our Primary Key). We’ll create a SaleDate as a Date/Time field. And let’s change the Default Value for the SaleDate field to “=date()”. That way we won’t have to keep entering in dates.
How about a CustomerID? We’ll make that a Number field. Let’s put in a Description field too, as a Text field. Let’s add a UnitPrice as a Currency value. The Quantity field can be a Number field. An IsPaid field can be a Yes/No value, and we want that to default to No.
Let’s add another field: IsTaxable as a Yes/No field, but let’s make the default value for this one Yes.
Finally, how about a PaymentMethodID field as a Number field (which we’ll create as a SubTable later on).
Let’s save this as SalesLogT.
There is no Primary Key defined, so click Yes, and Access will make our Primary Key the SalesLogID field.
Now we’ve got a couple things to do before we start putting some data in here. The first thing I want to do is set up that PaymentMethod table. So let’s close this to make another new table. Put in PaymentMethodID as AutoNumber, PaymentMethod as Text.That’s all we need.
Save it as PaymentMethodT with no Primary Key defined so Access will define one for you.
And let’s put some payment methods in it.
Let’s close it and save its changes. Let’s put some data in our SalesLog table now.
Let’s go ahead and close this. Now we’ve got our SalesLog table all set and we’re ready to move on to our next lesson!
Lesson 2: Sales Log Query
In this lesson, we’re going to create a SalesLog Query which means we’re going to use some mathematical functions to calculate our sales tax, our extended price, and some other good stuff. Now that we’ve got our SalesLog table set up, let’s set up a query to do some of the calculations for us. Click on Queries and then click on New. Select Design View. Now the only table that we’re going to need for this query is the one we just made. So select SalesLogT table and hit Add. Then close the window.
That’s all we need for now. Let’s bring all of the fields from the table into the query. Double-click on the star.
SaleLogT.* is now in the table and all the fields are in there.
The first thing that I want to do is the basic calculations for calculating our extended price. We’ve got our UnitPrice and our Quantity. How do I calculate the extended price? We’re going to create a new field called the (ExtPrice) ExtendedPrice: UnitPrice*Quantity.
Let’s save this query as SalesLogQ. And let’s run it to see what we get.
Alright. Beautiful - Look at that!
In that example, I showed you the multiply mathematical operator. There are some others you should be familiar with:
Back in our query, let’s figure out what our sales tax is. Let’s assume an eight percent tax rate for everybody. We know what our ExtendedPrice is and we know whether or not an item is taxable. So using our IF function (IIF function), we can put those together.
SalesTax:IIF(IsTaxable=True, ExtPrice*0.08, 0)
Let’s see if that works – and it does!
Now we can figure out what the order total is. OrderTotal: ExtPrice + SalesTax.
Let’s save that with the keyboard combination Control + S. Let’s run it and it looks pretty good!
Let’s clean that SalesTax column up to look like a currency. Back in Design View, right click on this column and click Properties to get the Field Properties window.
Let’s type in Currency for the Format of that field. Save it and run it again. It looks like it did the job, but if I click on it, it changes back to its original format. For real sales tax, you’re going to want to round that value off. So let’s do that.
Let’s go to a new column and call it the SalesTaxRound:ROUND(SalesTax,2) to round it to two decimal places.
Save it and run it to see what we get. Let’s make the OrderTotal based on this new SalesTaxRound.
I’m going to highlight the SalesTaxRound column and drag to the left in front of the OrderTotal.
And while we’re at it, we might as well format it. Right-click it and select Properties. Let’s format this guy as Currency. And we’ll change SalesTax in the OrderTotal to SalesTaxRound so that we’re basing our OrderTotal on the correct one.
And there we go. We’ve successfully rounded off our numbers in our query.
Lesson 3: Sales Log Form
We’ve built a SalesLog table and a SalesLog query with some math functions in it. Now, let’s work on a SalesLog Form to bring these things together. Go to Forms and click New to create a new form. Click Design View. Then select SalesLogQ query in the drop-down list.
Let’s select and bring in all of the fields.
I’m going to set this up to look like a continuous form like we’ve done before. But for the CustomerID, I want a customer drop-down. It’s already on our Contact form.
If I look at the properties for this box, the name of it is CustomerCombo and it’s tied to the CustomerID field. That means, I can just steal the CustomerID and drop it in our form.
Just copy it and paste into the SalesLog form.
With the ExtPrice field, I’m going to set the background color to light grey.
While we’re at it, let’s change the background color of the form.
Let’s change SalesTaxRound to SaleTax and make the OrderTotal field yellow so that it stand out.
Now we’ve got our PaymentMethod to deal with so we’ll make a combo box out of this guy. Let’s use the wizard to do that. Delete it and bring up the toolbox. Make sure your Wizards are on.
And let’s grab a combo box and drop it onto the form.
The wizard starts up. You want the combo box to look up the values in a table or query. Select the values from the PaymentMethod table. Bring over all the available fields and click next. Hide the key column and store that value in the PaymentMethod field in our Sales table. Label the combo box Payment, and then hit Finish. And there’s our combo box!
Highlight all the fields so that we can get rid of that sunken look. Go up to the special effects and pick the flat one and give it a black border.
There we go – nice and sharp!
Scroll down to the bottom of the form and drag it up. Let’s go into the properties of the form and change the Default View to Continuous Forms.
I want my form header and footer, so go up to View, and hit Form Header and Footer.
Let’s cut out all of our labels. Highlight them and hit Control + X. Click the Form Header and hit Control + V to paste them in.
Grab the line tool and drop it in the header so that you can drag it all the way out.
Drag up all the input fields and bring up the form footer.
Let’s save our form as SalesLogF and reopen it.
Oh, it’s a beautiful thing!
One thing that would be nice is a little total down at the bottom. So let’s throw that in during the next lesson.
Lesson 4: Sales Log Form 2
In this lesson, we’re going to do just a little bit more with our SalesLog form, including putting a total in the footer to total up your sales, and seeing the sales just for one customer. Now it would be nice to see a little total down at the bottom of the form. Let’s see how we do that.
Let’s go back to Design View. I’m just going to copy the OrderTotal field and paste it into the form footer and let’s see what we get.
Save it and preview it.
Okay. It’s showing me whatever record I happen to click on. Is there anyway I can get the form to add all those up? Well let’s take a look! Let’s go back into Design View and open up the Properties for this box. (Double-click on it).
Let’s call the name of this box SumOrderTotal, and instead of just linking this box to the OrderTotal field, let’s make it equal to the sum of all of the OrderTotal. Let’s try that.
Let’s save it and preview it. Look at that! It sums it all up!
Go back to Design View real quick and the properties so you can set the format to Currency. Save it, preview it, and look at that.
Here’s something else that’s neat. If I right click on one of the customers, I can Filter By Selection. It filtered the results just for that customer. Look at the total. The total is showing the adjusted total just for that customer.
Let’s see how we can see just a customer’s orders. Let’s open up the Customer form. Let’s make some more room on the bottom of that form.
Let’s grab a command button and drop it on the form. Let’s go to Form Operators on the Command Button Wizard, then Open Form. Pick the SalesLog form and select Open the form and find specific data to display. Link CustomerID on the Customer form to CustomerID on the SalesLog form. Type in Show Sales for the button’s text. Name the button ShowSales. Here’s our button.
Let’s save it and see if it works. Now I can see just this customers sales!
There’s the total and that’s that!
Lesson 5: Join Types
In this lesson, we’re going to learn about different query join types, how to join multiple tables together. We’re going to learn about inner versus outer joins. We’ve seen how joins work before. That’s when you take two tables and join them together in your query. For example, let’s say the boss wants to see a list of all of our sales with the appropriate customer information.
Let’s go into our queries and create a new one. Select Design View. And let’s bring in our SalesLog information and the Customer information. Hit Close. Notice that Access saw CustomerID in both tables, so it made the assumption that it was the same field and it linked them together. It created a join.
So let say I want to see the SalesDate, and the Description, and the UnitPrice from the SalesLog, and the CompanyName from the Customer table.
Let’s save this as SalesWithCustomerInfoQ and run it.
Looks good. But notice that I’m not seeing any customers in here that don’t have a sale.
Well this join type is an inner join that shows all the records from both tables where there is equal and related information. So let’s try to break this.
Go into your SalesLog table and sneak in a customer record.
Go back and run a query. Look at that - Our customer’s not on there! He’s missing because there’s no related record in the Customer table for him. To include him, we have to use an outer join. How do we set up an outer join? Let’s find out.
Let’s go to Design View and double-click on the join line to bring up the Join Properties window. Let’s look at the three options below. The inner join is created from the first option already selected. Let’s select option two: Include ALL records from ‘SalesLogT’ and only those records from ‘CustomerT’ where the joined fields are equal.
Look at that! That’s saying show me all the information in SalesLogT, and all the Customers that are equal to it. Let’s run it and see what happens.
We can see our mysterious balloon order – but there’s no company! That’s okay. Let’s go back into Design View and select the other option: Include All records from ’CustomerT’ and only those records from ‘SalesLogT’ where the joined fields are equal.
The arrow points the other way. Now let’s run it.
Look at that! There’s all of our customers and any sales that they happen to have. But what I really want is to go the other way.
Let’s say, show me all the sales whether or not they’ve got a customer attached to them.
This will tell me that we’ve got a problem here. This balloon order is missing a customer.
If I save this and open up the SalesLog form. You can see that yes, it is missing a customer.
Drop it down and select a customer.
And let’s change the order from a balloon to a scanner for $55.00.
Looking at our queries, our ContactQ shows missing customers. Customer number 6 is missing.
I can fix that easily by just changing this join to option two.
Now all my customers show.
Lesson 6: Global Relationships, Referential Integrity, and Cascade Delete
We talked a little bit about Global Relationships way back in Access 201. We’re going to talk a little bit more about them here. We’re going to go over Referential Integrity, which is making sure that the records between two tables are properly related to each other. And I’m going to show you how Cascade Delete works, where you delete one record in one table and it deletes all the related records in another table.
Let’s say you’ve got a customer and that customer’s got some sales in the system. You wouldn’t want to delete the customer’s record if he’s got sales in there because if you do, it’s going to throw all your numbers off. This is one example where you might want to set up a global relationship to enforce some referential integrity in your database.
Make sure you have your database window selected and click Tools – Relationships.
What do we want to see in here? We want the Customer table and the SalesLog table.
We’re going to click and drag CustomerID from the Customer table to CustomerID in the SalesLog table. There’s the Edit Relationships window. Check the Enforce Referential Integrity box to say records for one table should not be deleted if we’ve got records for the other one. Click on Create.
Look at that. We now have a one-to-many relationship between the Customer table and the SalesLog table. Let’s close this and save the changes.
If I go into the Forms and pull up the SalesLog form, notice that 123 Packing has a couple of sales. If I go into my Tables and go to the Customer table, I can click the plus sign next to 123 Packing, and I can see a new level of detail in the Customer table. If you’ve got Global Relationships set up between two tables, you can drill down to see the details from that second table. This is one benefit of turning on Referential Integrity.
If I try to delete 123 Packing, I get yelled at:
In other words, as long as 123 Packing has records in the SalesLog, I can’t delete it. I can still change the names of some of the records – I just can’t nuke the record.
Let’s say I add someone new.
If I click on the plus sign, he’s got no related records, but I can type one in here.
Let’s open up the SalesLog. There’s his order!
More importantly, I cannot delete that record but – let’s close our Customer table and go back into our Relationships. Double-click on the join to bring up the Edit Relationships window. Click Cascade Delete Related Records and hit OK.
Close the Relationships window and go back into our Customer table. If I click on Joe Schmoe and try to delete it – it’s gone!
What happened though, to that hat record? Let’s open up our SalesLog. The hat sale is gone!
What happened? Well, if you enable Cascade Delete, it will delete all related records. For the purpose of this class, I’m going to turn all this stuff back off and I’m going to delete the relationship.
Let’s take a moment now to review what we covered in class.
· We created a SalesLog table, a couple of queries to go with it and a SalesLog form.
· We learned about mathematical calculations in our queries – how to make new query fields.
· We learned about how functions and queries work and the IFF function .
· We learned about different join types – the inner join and the outer join.
· We learned about calculated totals in our forms.
· We learned about Referential Integrity.
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: