Access 201 Handbook

Access 201 Handbook
By Richard Rost

16 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 201
Course Handbook Supplement

By Richard Rost

Published By
Amicron Computing
PO Box 1308, Amherst NY 14226 USA
www.599cd.com

First Printing 9/27/2004
Copyright 2004 by Amicron Computing
All Rights Reserved

Welcome

Welcome to the 599CD Microsoft Access 201 Handbook. This class follows Microsoft Access 104.

This handbook is designed to be a supplement to the full 599CD video course for Microsoft Access 201. 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

Welcome 2
Table of Contents 2
Lesson 1. Introduction 3
Lesson 2. Relational Database Concepts 4
Lesson 3. Relational Contacts Table 10
Lesson 4. Contact Query 15
Lesson 5. Contact Form 26
Lesson 6. Miscellaneous 35
Lesson 7. Review 39

Lesson 1. Introduction

Welcome to Microsoft Access 201, brought to you by 599CD.com. I am your instructor, Richard Rost.

Objectives for today’s class:

· Relational Database Concepts
· Making our Contacts Table Relational
· New Contacts Query & Form
· Using a Combo Box to Select from a List
· Discussion on Global Relationships

In case you haven’t been building a database with the class videos, a copy of the database from Access 104 has been stored on your computer in the following folder:

C:\Program Files\599CD\Access 201\Help

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.

Pre-Requisites: Access 101, 102, 103, 104, Windows 101, 102, or 110. Word 101 and Excel 101 are also recommended.

Lesson 2. Relational Database Concepts

A relational database is a database in which the data is organized and can be accessed according to relationships. Why are relational databases important? Relational databases help to minimize errors and increase efficiency by eliminating duplicate data.

Here is an example of a one-to-one relationship. One customer to one vehicle.

Now, if the same customer buys a second vehicle, you now have a one-to-many relationship.

Now if the customer buys a third vehicle, you still have a one-to-many relationship, but you can see there is a lot of duplicated data.

If you start getting lots of customers with lots of vehicles, you’ve got a lot of duplicated data in your database. This equates to a lot of wasted space.

The way to fix this problem is to create two tables: one for customers, and one for vehicles.

We need to relate this information together. If we assign a Customer ID to Joe, then we can track each of his vehicles by storing his Customer ID along with each vehicle record. This creates our relationship.

If we put the other customers into the customer table, and their vehicles into the vehicle table, you can see how we can track them by use of the Customer ID.

In relational databases, there are two terms you should know: primary key and foreign key. We’ve already seen a primary key in action. This is a field like the CustomerID in the customer table – it’s the one unique identifier for each record in that table.

A foreign key is simply a primary key from another table. In the example above, the CustomerID would be a primary key in the customer table, but it’s a foreign key in the vehicle table. It’s a key, but it’s not in its home table.

The vehicle table has its own primary key, which we can call VehicleID. The CustomerID is a foreign key in the vehicle table. The key symbols above show the primary keys for each table.

There are four types of relationships between tables:

· One-to-one
· One-to-many
· Many-to-one
· Many-to-many

One-to-one relationships aren’t very popular. These are usually good for creating a second table to store additional data on a customer (for example).

One-to-many relationships are used all the time. This is the most common type of relationship.

Many-to-one relationships aren’t used often. You may have a list of vehicles that have a many-to-one relationship back to their manufacturer.

A many-to-many relationship requires a third table to act as a cross-reference table.

Lesson 3. Relational Contacts Table

Before we begin class today, I just want to stress (again) the importance of creating backup copies of your database. I am going to create a folder to store incremental copies of my database as I’m editing it. Before you make any major changes to your database, create a backup and tuck it away in this folder.

Copy and paste your database, and give it a new backup name. I’m going to call mine “PCResale.Net Customer Database – before 201” but you can give yours any kind of name you want. I like to give it dated names, like “PCResale.Net Backup 2003-07-15” which would be a backup from July 15th, 2003 (for example).

Now just drop that backup file in your backup folder, and you’re good to go. Of course, make sure you’re backing all of this (and all of your other files too) to a tape drive or a CD-R at least once a month or so. Yes, it can happen to you!

OK, now that that’s done, let’s edit our ContactT in design mode. Notice the FirstName, LastName, CompanyName, and PhoneNumber are all fields that are duplicated from our Customer table.

Look at the two tables with their data in them. You can see the duplicated fields.

Go to design mode in your Contact T. Let’s insert a row in front of the FirstName field. Right-click on the little gray box to the left of the field and select Insert Rows.

In the new row that appears, insert a field called CustomerID and make it of type number.

Very important: this new field is now a Foreign Key. It’s storing a value that’s a primary key from another table, therefore you have to use the data type Number, and not AutoNumber.

Save your table. Return to datasheet view. We now have to type in CustomerIDs for all of these records. Match them up as best you can. You can get these values by looking at your customer table.

Now we can delete the fields we don’t need from the ContactT table.

Select those fields and hit DELETE. You’ll be asked if you’re sure you want to delete them, say Yes. Be careful. There’s no way to get them back!

Go to table datasheet view. Notice that you have enough information now to tell what customer the contact is for. You have the CustomerID. All of the data does not need to be duplicated in the ContactT table.

Lesson 4. Contact Query

Go to queries. Run your ContactQ. Notice now that you’re getting an “Enter Parameter Value” box. This is because we deleted fields from the Contact table, and the query needs them, so it’s asking you for them.

Let’s design this query. Notice the missing fields are showing up as Expr1, Expr2, and so on.

Rather than trying to fix this query, let’s just delete it and start over. Go to queries, new, design view. Bring in both the CustomerT and the ContactT.

Notice we now have two tables in the same query. Access saw that we have the same field in both tables, so it assumes that these tables are related based on that field. It creates a relationship between the tables and draws a join line between them.

Note: if you didn’t get this join line automatically, check your spelling. If you don’t spell the field names exactly the same, the join will not be created. Also be mindful of spaces… CustomerID is different from Customer_ID. If this is a problem, stop the lesson and fix your tables.

You can delete a join line by clicking on it and hitting delete on your keyboard. Likewise, you can manually create a join line (if Access doesn’t create it automatically) by clicking and dragging the field in one table and dropping it on the field in the other table you want to relate to. We’ll cover this in detail in a future lesson.

OK, let’s say the boss needs a list of all of the customers and their contact information. Into your query, bring FirstName, LastName, and Phone from the CustomerT. Now, bring in the DateTime and Notes fields from the ContactT. Notice you can have fields in your query from more than one table.

Save your query as ContactQ. Run the query. Notice how you have the customer details along with each contact record for that customer.

Notice how each customer may be duplicated – once for each contact record. However, we have no duplicated data stored in our tables. This is significant.

Let’s add our CustomerID and ContactID to this query. When you double-click on them they come in to the far right of the query field list. Let’s move them over to the far left. Highlight them both by clicking and dragging on the little gray bar above them.

Now, let the mouse button go. Click on that same exact spot again. This time you’ll have a little white arrow. Click and drag the columns all the way to the left. This will move them out front. It’s just a matter of personal preference to have the ID fields all the way on the left.

Run the query. Notice the IDs.

Let’s recreate that FullName field we created before.

FullName: FirstName & “ ” & LastName

Run the query and check to make sure your FullName field works.

Now let’s go over to Reports and run the ContactR report that we created in the last class. Notice when I run it, I get a parameter value. That’s because we missed CompanyName in our query.

That’s OK. Just go back to your ContactQ query and add it in.

Run your report again. Oops. Notice I’m missing PhoneNumber now. I’ll go back to my query and add the Phone field in. When I try to run the report, I still get the missing field message.

Let’s go back and check the query. Run the ContactQ. Notice somehow I’ve got a field in here called Expr1004. What’s that all about?

It looks like I added the Phone field to my query twice. I already had it in my query before I added it again. Let’s delete one. That’s why you’re seeing that Expr1004 field. Access can’t show the same field twice, so it changes the name. The problem is that the report is looking for a field called PhoneNumber, not Phone. Access is picky that way. Let’s go and edit our Report. Open the ContactR in design mode.

Open up the properties for the PhoneNumber text box (right-click, properties).

Change the Control Source for this field to Phone. Notice in the drop-down field list, there is no PhoneNumber listed – the field doesn’t exist any more.

I’m going to change the Name of the box as well. Let’s make that Phone too.

Save it, close it, and reopen the report. Notice everything runs OK now.

You’ve now created a relational query that is feeding this report. Congratulations! You’ve taken a major step forward in your database skills. You now know how to create relational databases.

Lesson 5. Contact Form

Let’s take a look at our ContactT. Let’s add a new contact at the bottom. Let’s say Joe Smith called. Type in “Joe just called to talk about a new computer” in the notes. Notice the CustomerID for this record is zero (0). We would have to open up the customer table, find Joe Smith, and manually type in his CustomerID in order to keep the relationship here.

I want to be able to pick a customer from a list in – say – a combo box and have that ID stored for me automatically. Let’s create a Contact Form. Go to Forms, New, Design View. Use ContactT for our data source.

Bring over all of the fields into your form.

Let’s change the form’s background color to a light yellow. Let’s preview the form and see what it looks like with our data in it.

Open up your toolbox. Let’s place a Combo Box on our form so we can pick a customer instead of having to type in a CustomerID value.

The wizard starts. Let’s select I want to look up the values in a table or query. We’re displaying a list of customers from the CustomerT table. Click Next.

Where is my list of customers coming from? The customer table. Choose Table: CustomerT for your data source.

Next, which fields from the customer table do you want to use in your combo box? Select the CustomerID. The ID field should always be your first field. The next field you bring in should be CompanyName. The company name will be the first visible field in your combo box. I’m also going to bring over FirstName and LastName. They’ll show up only when you drop the box down.

Next you’ll see the fields as they will appear in your combo box when its open. You can resize the column widths here if you want.

Notice the box called Hide Key Column (recommended). If you check this box off you will now see the CustomerID. We really don’t want to see the CustomerID, so Access was doing us a favor by hiding it. So we’ll take the recommendation and check that box back ON.

Important: Note that the CustomerID has to be in the combo box. The CustomerID is the value we’re storing in the Contact table… so it’s got to be in there. We just don’t need to see it. So on the next screen, we want to pick Store that value in this field and pick the field we want to store the value in: CustomerID.

Mucho Important Concept:
We’re picking a customer from a list of all of the customers,
and we’re storing that customer’s ID in the CustomerID field
on the Contact table.

On the next screen, just give the combo box some label text. I’ll put in Customer:

Notice your combo box on the form.

Save your form, and go to form view. Notice you have a field displaying the CustomerID, and our combo box which displays the company name. These two fields are both bound by the same field – CustomerID. So changing one should change the other. I just did this to demonstrate for you how this works. Normally you wouldn’t leave both of these fields on here (in fact, we’ll delete the ID box in a few minutes). Just change the values in one right now and watch the other change – they’re bound to the same underlying field in the table.

Notice as you change the value in the combo box, the CustomerID changes. Notice also that the key column (CustomerID) is hidden. The second column is actually the first visible column, which is the company name. Other columns are shown only when the box is opened.

Now, delete the combo box, and run through this example again. Believe me – it makes much more sense the second time through!

Once you’ve created it again, you can now delete the CustomerID text box – we don’t need it. Go ahead and slide the combo box into its place. Resize all of your text fields so they’re a little bigger, and resize the form to fit the boxes. Left-align all of the text boxes. Let’s also make the background color for the ContactID gray. Save and preview it.

Since the customer combo box was added last, its at the bottom of the Tab Order. Let’s adjust our tab order to put it where it belongs. Go to design view, click on View > Tab Order. Manually reorder the fields, or just click on Auto Order. Notice that the box is called Combo6 in my database. The combo box wizard didn’t give us the opportunity to rename the box.

Let’s open up the properties for the combo box and give it a good name. Right-click on the box and pick properties. Let’s type in a Name of CustomerCombo.

Lesson 6. Miscellaneous

Let’s add our contact form onto the main menu. Design the MainMenuF. Open the toolbox. Using the command button wizard, create a button to open the Contact Form. We’ve done this before. I’ll name mine ContactButton.

Open the properties for that button and give it a ControlTip Text of “Open Contacts.”

It would be nice if we could open a customer’s record and then open up just the contacts for that customer. We can do that with a command button as well. We’ll do that in the next lesson (teaser!)

Let’s talk about Relationships. From the main database window, click on Tools > Relationships.

Add both the CustomerT and ContactT to the relationships window (this works just like a query).

Just like manually creating a relationship in a query, click on the CustomerID field in the Customer table and drag it over to the contact table, dropping it on the CustomerID field there.

The Edit Relationships window appears. There are a ton of options on here. For now, let’s just click on the create button. We’ll deal with a lot of these options in a future lesson.

Notice how we created the link between the two tables.

We’ve just manually created a Global Relationship between these two tables. At this point this isn’t entirely important, however you will see how it will become very relevant in the future. You can use this to create integrity in your database.

For example, let’s say you wanted to make sure that your users couldn’t delete any customers who have contacts in the system. You could set up this global relationship and then tell the database not to allow any customers to be deleted if they have related records in another table – like contacts, or even Orders (which we’ll be making in a future class).

This is just one example of the use of global relationships. At this time, I just wanted to expose you to this so you can see basically what it is. We’ll work with it in much more detail later. For now, let’s just go ahead and delete the relationship (click on the line and hit delete) and then remove these tables from the relationships window – just click on them and hit delete. Close the relationships window and do not save changes.

Lesson 7. Review

Review topics.

Tell us what you think. Log on to www.599cd.com/Survey and take a short survey about this course.

Take your skills check quiz at www.599cd.com/Test. If you pass, you can print out a Certificate of Completion.

What’s next? Visit www.599cd.com/Access for our complete list of Microsoft Access courses.

Need Help? Visit www.599cd.com/TechHelp for Microsoft FrontPage assistance.

Make sure you’re on our Mailing List. Go to www.599cd.com/MailingList for details.

Contact Us. If you have any questions, go to www.599cd.com/Contact for information on how you can contact us by phone, email, or live online chat.

This course, handbook, videos, and other materials are copyright 2002, 2003, 2004 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:

Amicron Computing
PO Box 1308
Amherst NY 14226 USA
www.599cd.com

Subscribe to Access 201 Handbook
Get notifications when this page is updated