ERROR #3704: Operation is not allowed when the object is closed.
Microsoft Access 102
Microsoft Access Training Videos on CD-ROM
Microsoft Access 102
Course Handbook Supplement
By Richard Rost
PO Box 1308, Amherst NY 14226 USA
First Printing 9/16/2004
Copyright 2004 by Amicron Computing
All Rights Reserved
Welcome to the 599CD Microsoft Access 102 Handbook. This class follows Microsoft Access 101.
This handbook is designed to be a supplement to the full 599CD video course for Microsoft Access 102. 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. Introduction 3
Lesson 2. Quick Review of Access 101 4
Lesson 3. Field Properties 5
Lesson 4. Searching, Sorting, Filtering 30
Lesson 5. Parameter & Multiple Criteria Queries 45
Lesson 6. Employee Table & Form 63
Lesson 7. More Mailing Labels 92
Lesson 8. Compacting & Repairing a Database 111
Lesson 9. Review 113
Lesson 1. Introduction
Welcome to Microsoft Access 102, brought to you by MyOnlineLearningPartner.com and 599CD.com. I am your instructor, Richard Rost.
Objectives for today’s class:
• Table field properties
• Searching, Sorting, Filtering
• Multiple Criteria in Queries
• Parameter Queries
• Creating an Employee Form
• Using a Picture Field
• Creating a Combo Box and List Box
• Copying Records
• Basic Command Buttons
• Creating Mailing Labels without Missing Data
• List of Customers with Missing Data
• Compacting and Repairing your Database
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, Windows 101, 102, or 110. Word 101 and Excel 101 are also recommended.
Lesson 2. Quick Review of Access 101
This lesson is a quick review of Access 101. We recommend you watch it if you have taken Access 101 recently. If not, take some time now to re-watch the Access 101 videos.
Note that if you did not complete the database from Access 101, a copy of our database will be store in the following folder on your hard drive after you’ve installed Access 102:
C:\ Program Files \ 599CD \ Access 102 \ Help \
If you do not have this file, you can also download it from our student web site for this class at:
www.599cd.com / Access / 102
Lesson 3. Field Properties
Begin this lesson by opening up your CustomerT table in Design mode.
Click on one of your text fields, like FirstName.
The Field Size for a text field is the number of characters reserved in the table for this particular field. 20 is a good size for FirstName. Not too many first names are longer than 20 characters.
Likewise, we can set the field size for LastName to 30 characters. Just click on the LastName field to change the focus.
CompanyName is fine at 50 characters. I’m going to set the Phone field to 10 characters. Likewise, I’ll set the Fax number field to 10 digits. Leave Address at 50. City can be 20. State will be set at 2 characters, for a 2 digit state abbreviation. ZIP will be set to 6 characters, leaving space for US and Canadian ZIP codes. Country I will set to 20.
The NumEmployees field is a Number field. The field size defaults to Long Integer.
There are many different kinds of Number types. Integers and Long Integers are counting numbers (0, 1, 2, 3, and their negatives). Bytes are really small integers from 0 to 255. If you need decimal points in your numbers, you can use singles, doubles, or decimals. We’ll discuss the specific details of these different number types later. Generally you’ll use Long Integer and Decimal (or Double if you have an older version of Access that does not have the Decimal option).
The Format property is good for changing how data displays on the screen. For example, with Date/Time fields, like our CustomerSince, we can change how this date/time is displayed. For this field, I’ll pick Short Date.
To see what this looks like, click on the View button to switch out of Design View over to Table View.
Access yells at me telling me I have to save first. Click Yes.
I’m getting yelled at again. Because I’ve shrunk the field sizes on some of my fields, Access is warning me that some data might be lost (truncated). Click Yes.
Now we’re in Table View. Scroll to the right to find the CustomerSince field. Notice the short date format.
Let’s go back to design view and change this to a Long Date…
Now switch back to table view. Notice the change.
You can also type in custom formats. Instead of picking from the list, try typing in: mm yyyy
Now switch to Table View. Notice that we get month and year only.
Try this one:
If you click in the Format Property box (the place we just typed in the formats) and hit the F1 key on your keyboard, you’ll get help on the Format property specifications.
Click on the Date/Time data type link. Scroll down and you’ll see a list of all of the date/time codes…
Set your CustomerSince field back to a Short Date.
Notice there are options available for the Currency data type…
Set the Format to Fixed and you can change the number of Decimal Places to 3.
For class, just set this back to Currency and Auto decimal places. Notice the Yes/No field also has a format of Yes/No, On/Off, or True/False.
Now, let’s set up our Phone field to show just the digits along with hyphens to break the number up properly. This way a number like 7165551212 will display as 716-555-1212. To do this, click on the Phone field, and set the Format property as shown:
Save your table and switch to Table View.
Notice when you click on the Phone field to edit the data, the dashes temporarily disappear.
Return to Design View. Click in the Format property box and hit F1 again to pull up the Help system. Click on the Text and Memo Data Types.
You will see the different codes for dealing with text formats:
Delete the format code. Let’s instead use an Input Mask. Type in the following:
If you hit TAB or move to a different property box, you will notice the code changes slightly. Don’t worry about it.
Save your table and switch to Table view. Notice that your data looks the same. However, if you click on it, notice the input mask remains in place.
Notice, in fact, if you click on a field that doesn’t have a phone number, you will see the input mask helps to guide the user with the correct format for the data.
Again, if you hit F1 in the input mask property for Help, you’ll see a list of the allowed characters for the input mask.
Another property is the Required property. You can set this to Yes to force people to have to enter in a value in a particular field.
A Default Value is a value that a field starts off with – but you can change later. Let’s click on the State field, and set the Default Value to “NY”. We’ll assume for class that the majority of my customers are from New York. If you don’t type the quotes in, Access will insert them for you.
Save your table and go to Table View. Notice that the next new blank record will start off with a value of NY in the state field.
Let’s set the default credit limit to $500.
Notice that new records will start with a $500 credit limit.
We can also set the default value for NumEmployees to 1.
You can set the Validation Rule to <5000 if you want to force the credit limit to always be “less than 5000 dollars.”
When you save your table, you get a warning message. Click Yes.
Switch to Table View. Try typing in a credit limit of $6000.
You get an error message saying that you’re violating the validation rule. Hit the ESCAPE key on your keyboard. Return to design view, and type the following into the Validation Text property:
Notice now if I type in an invalid credit limit, I get a more user-friendly error message.
You can also use more complex validation rule, such as:
Which is the same as saying: Between 0 and 5000.
The last property we’ll discuss today is Indexed. Indexed is an important property. Indexing is good for preventing duplicate values in a field. For example, our CustomerID is “Indexed (No Duplicates)” because we don’t ever want to have more than one customer with the same ID.
Indexing is also used to speed up searches and sorts. If you are planning on searching or sorting queries (for example) based on last name, you might want to index the LastName field. You can index it “Yes (Duplicates OK)” to allow for more than one of the same last name.
I would not index FirstName – I’m probably not going to be doing too many searches and sorts on it. I would index Phone and Fax. I would only index Address if I was doing a lot of mailing and needed to search and sort on the Address field often. The rest of the fields I would probably not index.
Lesson 4. Searching, Sorting, Filtering
Open your Customer table. For this lesson, pretend you have thousands of customers in your database. Let’s say we’re looking for one particular customer: Donald Barker. First, click on the LastName field.
Now, click on the Find button (it looks like a pair of binoculars).
Type in what you want to find…
Notice you can search in the LastName field, or the whole table. In our case, just search in LastName.
You can match your search to the whole field, any part of the field, or the start of the field. Let’s search for the whole field.
You can search all (the whole table), up (up in the table from your current record), or down (down the table from the current record). Let’s leave it set to All. You can optionally click on Match Case to force Access to care about upper v. lowercase letters (we don’t need to worry about this now). Also, don’t worry about “Search fields as formatted” for now. Leave it checked. We’ll discuss it later.
Click on Find Next.
Notice that our focus has moved to the record containing a LastName of Barker. If there were more than one Barker in our table, you could click on Find Next again to move to the next one. The Replace feature works almost the same way, but you can type in text to replace “Barker” with.
For example, I could type in “Jones” and click on Replace All to replace all instances of Barker with Jones. You could also replace them individually with the Replace button.
I’m not going to click on either of these for now. Let’s leave Don Barker’s record alone. (We wouldn’t want him getting mad at us). Feel free to play with Find and Replace on your own. I cover it in great detail in my Microsoft Word courses.
In addition to searching records, you can also Sort them quickly and easily. Just click on the field you wish to sort by, and then click on either the Sort Ascending or Sort Descending buttons.
After clicking on Sort Ascending, notice that all of the records have been sorted alphabetically.
In addition to searching and sorting, you can also Filter your records to only show certain records, or not show certain records, based on their values. To quickly filter your table to only show customers from New York, find one of them, right-click on the state field, and select Filter By Selection.
Now notice that your table is filtered.
You can click on the buttons on the toolbar to remove the filter, re-apply the filter, and so on. To be honest, I never use the buttons. I always use the right-click menu.
I’m going to right-click anywhere on the table, and select Remove Filter/Sort.
Let’s say you want to show everyone but the customers from NY. Again, right-click on one of them and select Filter Excluding Selection.
Now, notice the New Yorkers are gone. The records still exist, but they’ve been temporarily filtered out (hidden from view).
Remove the filter. If you don’t want to actually have to look for a value, you can right-click on the field you want to filter by, and just type in the value next to Filter For:
Notice now I only see records where the city is Buffalo.
Again, remove the filter. You can also filter by parts of a field. For example, you can highlight just the “716” in any phone number field, right-click on it, select Filter By Selection and now you’ll only see phone numbers that include “716” somewhere in them.
Note that the “716” can appear anywhere in the field, and not necessarily at the beginning (although all the ones shown do).
Remove the filter again. Close the Customer table and open the Customer form.
Again, to filter the records so you only see New Yorkers, right-click on the State field of a customer from NY, and select Filter By Selection.
Notice now at the bottom, you only see record 1 of 2 (Filtered). This means that some records have been filtered out.
You can remove the filter by right-clicking on any field and selecting Remove Filter/Sort.
You can also sort based on a field. Right-click on your Credit Limit field and select Sort Descending. You will now see the records sorted so the customers with the highest credit limits show up first.
Notice the sort in action:
You can also search in your form. Click on the last name field. Click on the Find button.
Type in “barker” again in the find window, and click on Find Next. Notice that you’re taken to Donald Barker’s record.
That’s how you can do searches, sorts, and filters in your tables and forms. You can also apply most of these techniques to searching inside of queries as well.
Lesson 5. Parameter & Multiple Criteria Queries
Open your CustomersFromNYQ query from Access 101. We want to edit this query to see customers from NY and from Pennsylvania. Change your query as follows:
Upon running my query, however, I discover there are no customers from PA in my table. Let’s change it to NY or CA.
Now, run the query. You should see NY and CA customers.
Another way to phrase this query would be to put the CA in the row below the NY. This is the “OR” row and works the same way.
Running the query now will yield the same results. OK, go ahead and delete the “CA” from the OR row and let’s try something new. The boss now says he wants to see customers from NY who are Active. So, add the Active field to the query.
Type in True in the Criteria row, next to “NY”. You can use the words True, Yes, On, or even the value –1 here. I prefer True.
Running the query now shows us all of the customers from NY who are active.
Let’s cheat and mark Joe Smith inactive. You can do this – remember this is live data. Go ahead and re-run your query (go back to design view then run it again). You now see only Richard Rost.
Going across the Criteria row is treated as an AND condition. The statement above says “show me all of the customers from NY and are Active.” Now, if we were to change this again like this…
The statement now says, “show me all of the customers who are from NY and are Active OR who are from CA.” For those of you who love algebra, the statement would look like this:
(NY AND Active) OR (CA)
Yes, you would interpret each line to be inside of parentheses. Take the whole line, read it across, and then apply the OR conditions from successive rows. That’s why you can put an “OR” condition inside of one criteria. For example, earlier, when we had “NY or CA” on one line, that would be treated on the same level as the True… like this:
(NY or CA) AND Active
If I run the query the way we have it now, I get this:
Notice the Californian isn’t active. That’s because the second row doesn’t require him to be. If you want each state to have to be Active, just change the query as follows:
Let’s save this query with its own unique name. Click on File > Save As…
Save this as shown…
Now the boss wants a list of customers from NY who have credit limits of at least $200. We can again start with the CustomersFromNYQ as a template. Let’s copy and paste it to create a second query. Click once on the query. Hit CTRL-C on your keyboard to copy it. Then, hit CTRL-V to paste. Or, you could right-click to copy and paste (whichever method you prefer). Name your query as shown:
Open this new query up for design. Bring CreditLimit into the query.
In the criteria row under CreditLimit, type in: >200
This “greater than 200” will yield a list of customers with credit limits higher than 200.
Now the boss wants to see all customers from NY and those customers who have $200 or higher credit limits. Adjust your query as shown:
Now when we run our query, we have a much longer list of all NY customers, and all customers who have $200 credit limits.
This query shows you all customers from NY with a $500 credit limit, OR all customers anywhere with a $1000 credit limit:
This query will show you all customers who are from NY, or have a credit limit greater than $1000 and less than $2000.
Remember, you can also use the Between keyword. This query will show all customers from NY plus all customers with credit limits between $200 and $500.
Now the boss says to get rid of the credit limit, and only show customers who have been customers since 1998 or longer. You could just delete the CreditLimit field by highlighting the column and hitting DELETE on your keyboard.
It’s easier, however, just to change the field. Click on the drop-down box and simply pick a different field name.
Pick CustomerSince. For the criteria type in: >#1/1/1998#
Notice that Access will put # symbols around the date. If you have an older version of Access you will have to put them on manually. Run the query and you will see a list of anyone who is from NY or has become a customer after 1/1/98.
You can do the same thing with text fields. DELETE the CustomerSince field from your query. Remove the “NY” criteria from the state field. Let’s say you just want to see a query of anyone whos last name starts with the letter “S.” In the criteria field for LastName, type in: LIKE “S*”
The LIKE keyword allows you to use WILDCARD characters. The asterisk * says you can insert any type and number of characters here. So, this criteria says, basically, show me a list of all LastNames that begin with the letter “S” and have any type or number of characters after it. Here’s what you get:
We’re going to cover wildcards more in a future lesson. I just wanted to expose you to it today. Play and experiment with it more if you’d like.
Next, I would like to be able to make a query that asks the user what state they want to see users from – so we don’t have to make 50 different queries, one for each state.
Once again, open your CustomersFromNYQ for design more. Get rid of the “NY” criteria. Replace it with: [Enter the State:]
It doesn’t matter what you put inside the square brackets. This is your prompt for the user, as you’ll see when you run the query:
See how you get a dialog box with your prompt in it? The user is prompted to enter a state. I’ll type in CA and press ENTER. The query results are then displayed:
You now see customers from CA. The blank row on the bottom is just our new record – you can ignore it. If you run it again and type in NY, you’ll see all the New Yorkers. Notice how the user types in the parameter for this query. This is very powerful because now your queries can interact with the user. If you type in something that doesn’t exist in your database, like “ZZ” you’ll just get no records:
Let’s save this query (File > Save As) as CustomersFromAnyStateQ.
Notice now that I (or any user in my office) can run this query right from the database menu.
Keep in mind, you can combine these parameters with regular criteria (or even other parameters), such as:
This will show all Active customers who are from the state your user types in.
Lesson 6. Employee Table & Form
Let’s create an Employee Table. Go to Tables, click on New, Design View. Enter in the following fields:
• EmployeeID AutoNumber
• FirstName Text (20 char)
• LastName Text (30 char)
• Title Text (20 char)
• Email Text (100 char)
• HomePhone Text (10 char) Input Mask: 000-000-0000
• WorkPhone Text (10 char) Input Mask: 000-000-0000
• Address Text (50 char)
• City Text (20 char)
• State Text (2 char)
• ZIP Text (6 char)
• Country Text (20 char)
• SSN Text (9 char) Input Mask: 000-00-0000
• Active Yes/No
• Picture OLE Object
Save your table as EmployeeT. Let Access create your primary key for you. It will set the EmployeeID as the primary key. That’s what we want. Let’s put one employee record in it for now. Here are the values I entered. See if you can figure out what I’ve done here:
Joe, Employee, Stock Boy, joe@XYZ.com, 716-555-6666, 716-555-4443, 101 Main, Buffalo, NY, 14220, , 092-77-6666, Active YES, no picture.
Did you get all that? I typed them in a simple comma-delimited format. We’ll talk more about this when we get into data import and export. Don’t worry – your values don’t have to be exactly the same ones I entered.
We’ll enter in the picture when we get to the form. For now, save your work and exit to the main database window.
Let’s make the Employee Form now. Click Forms, New, Design View. Pick the EmployeeT table. Click OK.
The first thing I have to do is change my background color. I can’t stand the gray. Let’s go with light green.
Make sure your Field List is open. If not, open it up from the toolbar.
Bring over all of your fields onto the form. Remember how to do this? Double-click on the titlebar of the Field List to highlight all of the fields. Then, drag them all over to your form.
And now you should see all of the fields aligned on your form. Close the Field List. We’re done with it. Let’s slide the EmployeeID field up and set its background color to gray. I like to make any fields that are locked or unchangeable look gray so the user knows he can’t change the value.
Let’s do the same trick we did in the last class with the name field:
Move up and resize Title and Email. Do the same trick for Home and Work phone:
Let’s move up the Address fields, as well as the SSN and Active fields.
Picture fields (OLE Objects) always come in huge. Let’s shrink it down some.
Let’s save the form as EmployeeF.
Let’s go back to our EmployeeT table and add a Notes field.
Save the table, return to the Form design. Open the Field List. Put the Notes field on your form. Do the “finger” trick we learned in the last class to move the Notes label on top of the form, and place it at the top right of your form.
You can also make your notes field yellow with the shadow effect like we did in the last class. Now, get rid of the extra space on the bottom and to the right of your form. Save your work, and close the form down. Now, re-open it from the database window.
Now we want to insert a picture into the picture field. I have a picture of myself that I’ve saved as an image on my desktop. I’ll use this (you can use whatever picture file you have on hand).
Now, with your database open as shown in the last image with the desktop visible, just click and drag that picture right to the picture field on your form, and it will be inserted…
Let’s see another way to insert a picture. Hit DELETE to remove the picture from the box. Instead of clicking and dragging a picture, right-click on the picture box and select Insert Object…
Click on Create From File and then Browse to select your image file. Click OK when done.
The same image is now inserted. Notice how the image is cropped, however. You’re only seeing the upper-left corner of it. Let’s change the properties of the picture box so that it will fit the image inside the window. Return to design view. Right-click on the picture box. Select Properties.
The properties window opens up for this picture box. There are a ton of properties in here. We’re not going to cover them all today. Find the property called Size Mode. Notice it’s now set to Clip. That’s the default, and it means it will clip (or crop) the image and only show a part of it. Changing this setting to Stretch will stretch the image to fit inside the window, but it might distort the image. Let’s select Zoom, which will zoom the image in or out so it fits in the window, but doesn’t distort it.
Close the property window. Save your form. Return to form view. Now you’ll see the picture zoomed out so it fits in the window.
Now the boss comes up to us and says that we need to add Gender to our form to track whether each employee is male or female. First, add the field to the table. Let’s make it a 1-character text field. We’ll store either an “M” or an “F” in the table. A blank value could indicate we don’t have that data… which would be more helpful than a simple yes/no field where we couldn’t tell that the field was blank (see, you’ve got to think about these things when building a database. It could make a big difference).
Let’s put Gender on our form now, but not as a standard text box. Let’s open up the Toolbox.
Let’s find the Combo Box control.
Click on the Combo Box control, don’t hold it down (you’re selecting a tool). Now, click somewhere on your form to place the Box. The Combo Box Wizard will start up.
Select the second option “I will type in the values that I want.” Click Next.
We want 1 column for our box, and type in the values that will appear in the box below. Put an “M” in the first row, and an “F” in the second one.
You could optionally make 2 columns, and show Male and Female as well. This would appear when the user drops the box down (opens it). You’ll see how this works in a minute. Click Next.
Now, of your two columns, which one do you want to store in your table? We want to store the value from column 1 (the M or F). So pick Col1 and click Next.
We want to store that value in our Gender field, so select “store that value in this field” and select Gender from the list of fields. Click Next.
What label would you like to go next to your combo box? Type in “Gender:” and click Finish.
And now, notice your combo box on the form. You may also hear it referred to as a “drop-down” box.
Save your form, go to Form View, and then you can see how your combo box works.
You can also use the same wizard to create a List Box which is almost the same as a combo box, except the list is always “open” – unlike a combo box that is usually closed. Also, combo boxes allow for you to type in values (which we’ll cover in a future course). You can find list boxes right next to combo boxes on the toolbox.
You can change a combo box into a list box by right-clicking on it, and selecting Change To > List Box. Save your form, and look at it in Form View to see what it now looks like.
Change it back into a combo box by right-clicking on it and selecting Change To > Combo Box.
If you want to copy a record, select the entire record by clicking on the record selector which is the big gray bar to the left of the form.
You can now copy the record, using the keyboard (CTRL-C), by right-clicking, or by using the toolbar. Use whichever method you’re most comfortable with.
Click on the Go To New Record button at the bottom of the screen.
And now you can click on Paste to paste the data in to record 2.
Now you can change the necessary information over to Jane’s details.
The Tab Order is the order in which you move while you’re tabbing through fields. You can edit the tab order from design view by clicking in View > Tab Order.
Let’s say we want to move the Picture field to the bottom of the tab order. Scroll down through the list of fields and find Picture. Click on the little gray box to the left of it to select it.
Now, click on that same spot again and drag it down to the bottom of the list.
Now click OK. When you return to the form, you’ll notice that Picture will be the last field in your tab order as you tab around the form fields. For practice, go to your CustomerF form and move the Notes field down to the bottom of the list.
Next, while we’re still in the Customer form, I don’t lke the way the three fields at the bottom of the form all line up to the right side of the field (as numbers, dates, and currencies will do by default). I would like to change them to line up to the left.
Draw a box that touches all of the text boxes (or at least the ones you want to change). Remember how to do this? We covered it earlier.
Now on the formatting toolbar, click on the Align Left button.
Return to Form View and you’ll see that all of the fields are left-aligned now.
Let’s go back to our Employee form. Enter in some notes for the employee. Notice as you’re typing that the box will scroll down for you to enter pretty much as much information as you want (yes, there is a limit, but its many thousands of characters).
If you want more space on the screen to see what you’re typing, just hit SHIFT-F2 and a larger Zoom window will appear.
Let’s put a Command Button on our form so that the user can click on a button to close the form. This is the first of many different types of command buttons we’ll work with. In design mode, on your Employee form, click on the Command Button on your toolbox.
Now click to place a command button on your form.
There are a bunch of different actions that command buttons can take. They are grouped into different categories. Click on the Form Operations category, and then Close Form. Click on Next.
Now you can select between two different pictures for your form, or you can put text on your button. I like the picture of the stop sign. Click Next when you’ve made your selection.
Type in a meaningful name for your button. I’ll make mine StopButton. This is optional, but it will help you later when you get into programming! Click Finish when done.
Save your form. Return to Form View. Click on your button. The form should close.
Lesson 7. More Mailing Labels
In Access 101 we created mailing labels for our customers using a Report wizard – the Label wizard. What happens, however, if we have customers who are missing address data – like a ZIP code or even street address. In this lesson we’ll make address labels that won’t print labels for customers who are missing vital information.
First, let’s cheat by setting up the data in our table to produce the desired results. Open up your Customer table and intentionally delete one customer’s address, another’s state, and a third customer’s ZIP code.
Now, let’s set up a query to only show us customers who are not missing any address data. Click on Queries and then New.
Select Design View. Click OK.
Select the CustomerT table. Click Add. Then click Close to close the Show Table window.
Bring into your query all of the fields we’ll need for making mailing labels: FirstName, LastName, Address, City, State, ZIP, Country.
Run the query. Notice you are seeing customers with missing data.
To see a list of all records where the Address field is not blank – or NULL – then type in “Is Not Null” in the criteria box.
Run your query and notice the guy with the missing address is gone.
Put the same criteria in the City, State, and ZIP fields.
Run your query now, and you’ll see that all customers with missing data are gone.
Let’s save this query as CustomersWithFullAddressQ.
Now, let’s say you want to generate a list of customers who are missing data. You might want to give this list to your secretary to have him call each customer to get the missing data. Create another new query from scratch. Bring in your customer table and the same fields as before. This time, type in Is Null in the address criteria.
Run the query. Notice you have the one customer who is missing his address.
If you put Is Null in the criteria field for City on the same row as Address, you’ll be left with a list of customers who are missing an address and missing their city – which in this case is no records.
What you want to do is put the Is Null for city down one row in the next OR row. This will show all customers who are missing an address OR are missing a city.
Save this query as CustomersWithMissingDataQ.
Run the query. Notice you have two customers showing – one who is missing an address, another is missing his city.
Now do the same thing for State and ZIP.
Run it and you’ll see everyone who is missing any of this data.
Now let’s build some new mailing labels utilizing our new query of customers who aren’t missing address data. We used the Label wizard in the last class. I’ll review it briefly now. Click on Reports, then New.
Select the Label Wizard and then pick the CustomersWithFullAddressQ query from our list of data sources.
Click OK. The wizard will start. Select the Avery 5160 labels we used last time. Click Next.
The default font is fine. Click Next.
Set up your prototype label just like in Access 101. Click Next.
Sort by ZIP code. Click Next.
What name do you want for the report? Type in CustomersWithFullAddressR. [Editor’s Note: there is actually a typo in the video. I ended the report name with a Q instead of an R. Reports should end with R. Again, this is just Rick’s way of naming items – it won’t effect the functionality of the database.]
Click Finish. When the report runs, notice you now only have three address labels, but these are full and complete addresses – none of the customers who are missing data are shown.
Now let’s build a report for our secretary showing the list of customers who are missing data. We’ll build this report from scratch. Again, from the database window, select Reports and then New. This time select Design View and pick CustomersWithMissingDataQ as our data source.
Notice now that you’re placed inside of a blank report. This looks very similar to a blank form.
The only major difference in the design of reports (at this point) is that reports have two extra sections… a page header and a page footer. These are sections that will repeat at the top and bottom of each page in our report, and we’ll deal with these later.
If you don’t have your Field List showing, open it up from the toolbar (just like with forms).
Just like with forms, double-click on the titlebar of the field list to highlight all of the fields, and then click and drag them all over to the Detail section of the report.
Close the field list. I’m going to resize the detail section by clicking and dragging the gray horizontal bar down just a bit.
Let’s save this report as CustomersWithMissingDataR.
Let’s close it down, and then double-click on it from the database menu. This will open it up in Preview mode.
And there we go! You’ll see a nice formatted report with all of your customers who are missing data.
Lesson 8. Compacting & Repairing a Database
Compacting your database is a way of squeezing out any unneeded empty space that build up in your database. Unused portions of notes fields, deleted records, and other “junk” can create this wasted space. Think of this like stepping down on the garbage in a trash can. Of course, a big database is a slow database, so we want to keep our databases as small as possible by compacting regularly. I like to compact my database once a week.
Repairing your database is actually part of the same process. Sometimes errors creep into your database. Problems can occur if, for example, someone turns off the computer while the database is in the middle of a write operation – or if you have intermittant problems with network connectivity and your database is on a server somewhere. Lots of different problems can cause corruption in your database. Fortunately Access has a good built-in repair utility.
From inside your database, click on Tools > Database Utilities > Compact and Repair Database.
Note that in earlier versions of Access, this was two separate processes. In Access XP, they’re both part of the same function.
This will only take a few seconds for our class database (because it’s tiny) but if you have a large database (say, a few hundred megabytes) this could take a few minutes… especially if you have a slower computer. When it’s done compacting, you’ll be placed back at the database window.
If your database is corrupted so bad that you can’t even open it, you can try to repair it. Just open Access, but don’t open your database. Access may even ask you if you want to try to repair the database when you try opening it.
Again, click on Tools > Database Utilities > Compact & Repair. You will be given a browse window to select your database. Then, you’ll be asked where you want to compact your database into (the window title says Compact Database Into).
If your want to make sure you don’t accidentally lose what data is there, you can type in a different file name. I’m just going to go ahead and compact over the existing database file (which is OK). Click on the PCResale database and click OK.
Rick’s Big Bold Underlined Note: of course, you should be making regular nightly and weekly backups of your important data so that when (not if, but when) you experience catastrophic data loss you have your database files backed up on another computer, or on tape or CDR somewhere. Yes, I thought it couldn’t happen to me, but it did several years ago. See www.amicron.com/netbackup for my story.
Lesson 9. Review
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:
PO Box 1308
Amherst NY 14226 USA
Microsoft Access 102
Microsoft Access Training Videos on CD-ROM