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 104
Course Handbook Supplement
By Richard Rost
PO Box 1308, Amherst NY 14226 USA
First Printing 9/24/2004
Copyright 2004 by Amicron Computing
All Rights Reserved
Welcome to the 599CD Microsoft Access 104 Handbook. This class follows Microsoft Access 103.
This handbook is designed to be a supplement to the full 599CD video course for Microsoft Access 104. 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. Contact Table 4
Lesson 3. Contact Query 8
Lesson 4. Contact Report 16
Lesson 5. Miscellaneous Topics 44
Lesson 6. Review 49
Lesson 1. Introduction
Welcome to Microsoft Access 104, brought to you by 599CD.com. I am your instructor, Richard Rost.
Objectives for today’s class:
· Build a Contact Table
· Contact Query
· Contact Report
In case you haven’t been building a database with the class videos, a copy of the database from Access 103 has been stored on your computer in the following folder:
C:\Program Files\599CD\Access 104\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, Windows 101, 102, or 110. Word 101 and Excel 101 are also recommended.
Lesson 2. Contact Table
In this lesson we’re going to build a contact table. A “contact” is every instance of communication with a customer – via phone, in person, email, etc. We want to create a contact history for each customer. Begin by opening your database. Open a customer. If you want to track each contact with this customer, you could use the notes field. But that’s not a very good way to do things. If you wanted a report of all of your contacts for, say, October 2001, you couldn’t do it.
Let’s create a new table to store contacts. From the database window, click on Tables, New, Design View. Create a table with the following fields:
· ContactID AutoNumber
· FirstName Text
· LastName Text
· CompanyName Text
· PhoneNumber Text
· DateTime Date/Time
· Notes Memo
Save your table as ContactT. Say “Yes” to create a primary key (ContactID).
Switch to table view and put some data in. It would be easier if we could see our customer data too, so I’m just going to open up both tables – one on top of the other – so I can see them both.
Type in one sample record:
· FirstName: Richard
· LastName: Rost
· CompanyName: Amicron
· Phone: 837-4685
· DateTime: 9/4/03 12:30 pm
· Notes: Richard called and asked some questions
Looks good. Let’s type in another.
· FirstName: Suzi
· LastName: Jones
· Phone: 562-436-5146
Hold on one second. I’m sick of typing in dates and times. Wouldn’t it be nice if the database could automagically fill in the current date and time into this field? It can. Switch to Design View.
Click on the DateTime field and then go down to its Default Value property. Enter in the following as the Default Value:
Save your changes and return to table view. Notice the DateTime value for the next blank new record is set to the current date and time.
Note that the Default Value property won’t change any existing records – only new ones.
Note you can also use the =Date() function to get just the current date (at midnight).
Let’s finish entering Suzi’s record:
· FirstName: Suzi
· LastName: Jones
· Phone: 562-436-5146
· DateTime: 9/4/03 12:31 am
· Notes: Suzi wanted warranty information
One more contact…
· FirstName: Richard
· LastName: Rost
· CompanyName: Amicron
· Phone: 837-4685
· DateTime: 9/4/03 12:32 pm (automatic this time!)
· Notes: Richard wanted sales details
Go ahead and enter in a few more contacts… perhaps five or so extra contacts.
Lesson 3. Contact Query
OK, I’ve entered in 10 or so additional contacts. Make sure you have a couple of different dates in your contacts.
Close your contact and customer tables. We now want to create a query showing all contacts between two dates. Go to Queries, New, Design View, OK. Bring the ContactT into your query. Close the Show Table window. Bring the DateTime, FirstName, LastName, CompanyName, and PhoneNumber fields into your query.
Save your query as ContactQ. Run the query. You’ll get all records. Go back to design view. Now let’s limit it to only see the contacts from one date – I’ll pick September 4th, 2003. Type in “9/4/03” into the criteria row.
Notice when I hit TAB or ENTER, Access puts # pound signs around dates. (Earlier versions of Access don’t do this – you have to do it manually). Run the query.
Notice your query returned no records. Why? Because your criteria said “show me all records where the date/time is exactly 9/3/03 at midnight.” You need to make sure to include all of the records for the rest of the day.
Here’s one way to do it:
Between #9/4/2003# And #9/5/2003#
This will also mistakenly include contacts from 9/5/03 at exactly midnight. Run the query:
Here’s a better way to do it:
>= #9/4/2003# And < #9/5/2003#
This method assures you that you’ll get no records from 9/5/03.
You can also make this into a parameter query and let the user type in the dates when the query runs:
>= [Start Date] And < [End Date]
Now when the query runs, the user can type in any dates he wants.
I typed in 9/1 and 10/1 for my parameters, and got all of the records from September. For now, let’s hit Undo and go back to the previous static dates for our criteria.
Now, the boss wants the FirstName and LastName together as one single field. Go out to the end of the query (the last column). Type in the following at the top of a new field:
FullName: FirstName & LastName
This says, “create a new field, call it FullName, and it will be set equal to the concatenation of the FirstName and LastName fields.”
To concatenate is to put two or more pieces of text (such as these text fields) together end to end.
When you hit TAB or ENTER, Access puts brackets around the field names. That’s OK.
Run your query.
Notice your new column. The names are smashed together without any spaces between them You can add a space by changing your new field just slightly:
FullName: [FirstName] & “ “ & [LastName]
This says, “create a new field, call it FullName, and it will be set equal to the concatenation of the FirstName field, a “space” character, and then the LastName field.”
Note: you can put any characters you want inside of those quotes and those will appear in the field.
Run the query again.
Notice the names now have a space between them.
You can also create a “LastName, FirstName” listing:
FullName: [LastName] & “, “ & [FirstName]
Run your query:
Let’s format the phone number field to appear differently. We’re going to apply the formatting to the query. Go to design view. Right-click somewhere in the PhoneNumber field and select Properties.
Type in your format for the phone number field:
Close the property window. Run your query. Notice the new format for the phone numbers.
Go back to Design View and add the Notes field to the query. Now it’s ready to feed a report.
Lesson 4. Contact Report
Click on Reports, New, Design View. Base the report on your ContactQ that you just built. A blank new report window opens.
If it’s not already open, go ahead and open up your Field List window.
Bring all of the fields from the field list over to the detail section.
Close the field list – we’re done with it. Arrange these fields one over the other – horizontally. We’ve done this before. You can delete the single FirstName and LastName fields. We don’t need them (we have the FullName field).
Save this report as your ContactR. Let’s preview it and see what it looks like.
Not real inspiring, but we’ve got a lot of work still to do. Return to design view. Let’s cut the labels out and paste them into the Page Header section. Move the text boxes up to fill out the empty space.
Delete the Notes label. Move the notes textbox up and make it wider.
Let’s grab the Page Footer bar and drag it up to shrink up the detail section.
Let’s preview it again to see our changes.
Looks a lot better.
Move all of the fields except DateTime over to the right, and resize DateTime so the date and time properly displays.
Save it and preview it again Notice my pattern here… make a change, preview it… make another change, preview it. You’ll do this a lot. A good deal of Access report and form design is trial and error… depending on how picky you are. I’m real picky.
I’m going to also Align Left the DateTime field text, so it looks better.
Spruce up the labels… remove the colons and make them more English-friendly. The first thing I tried was changing my DateTime label to: “Contact Date & Time” and look what happens:
It’s hard to see from the screen shot, but what you get is:
Contact Date _ Time
That’s because Access uses the ampersand & character for special codes. If you want to display an ampersand in a label, you have to type two of them together, like:
Contact Date && Time
It will display properly now. Get rid of the rest of the colons and finish editing the rest of the labels.
How about putting a horizontal line under your labels. Get a line object from the toolbox and draw a line out at the bottom of the Page Header section. First, make your page header section a little bigger so you have some room to move.
Now, open up your toolbox and click on the Line object.
Click and drag a line out – try to keep it straight (unlike my screen cap picture!) If you don’t get it straight, just delete it and try again. If you need help keeping it straight, just hold the SHIFT key down before you click to place it… the line will be forced straight.
When you’re done placing and arranging your line where you want it, move the detail band back up to remove the empty space, and then preview your work.
It’s truly a work of art! Preview it.
Let’s shrink up the notes section a little bit and shrink the detail section too.
Notice in the image above, I have a problem. One of my contacts is real long and is getting chopped off. Wouldn’t it be nice if I could tell the Notes field to get bigger if it needs to? Return to design view. Right-click on the Notes box and bring up its properties. Change the Can Grow and Can Shrink properties to Yes.
Preview your report. Notice that the Notes box now expanded vertically to show all of the text, but the other boxes did not shrink up.
Return to Design View. Right-click on the Detail Section band, and bring up its properties.
Notice the detail section has Can Grow and Can Shrink properties as well. The detail section, by default, can grow to fit its contents, but Can Shrink is set to No. Change it to Yes and see what happens.
Preview your report. Notice all of the notes sections shrink up nicely.
Now the boss wants all of the contacts for each custom together. First, rearrange your report to put the contact name over to the left.
Click on the button on the toolbar that says Sorting and Grouping.
The Sorting and Grouping window appears.
To do a basic sort, you would just specify a field name and choose ascending or descending for the sort order.
Preview the report now, and notice that the report is sorted by FullName.
Return to design view. Open the sorting and grouping dialog. Let’s create a Group Header by changing the Group Header property to Yes.
Close the Sorting and Grouping dialog. Notice there is a new section on our report. This is a header for the FullName field. This means that the header will display once for all records that share the same FullName.
Now, cut the FullName field out of the detail section, and paste it into the FullName Header section.
Notice now that my name shows up once, and all of my contacts show up below me in my “section.”
Return to design view, sorting & grouping. Let’s turn on the Group Footer.
Let’s put a horizontal line in the footer (tip: you can copy and paste the line we created in the page header).
Notice the line after the group.
Let’s go back and edit our ContactQ which is the query that feeds this report. Let’s remove the criteria so we get all the records. Edit the ContactQ in design mode. Delete the date/time criteria.
Preview the contact report now, and you’ll see all of them.
Notice now I’ve got two pages to my report, but on the bottom of page one is the customer’s name, and his contact details were split onto page two.
You can force the section to stay together by changing one property. Go back to design view, sorting and grouping. By changing the Group Together property, you can force the whole group to stay together (the header and all of its records) or just the header and the first detail. Let’s pick Whole Group.
Preview the report now and notice the whole group is now on page two.
In addition to a page header and footer, which repeat at the top and bottom of each page, you can also have a Report Header and Footer which display only once at the top and bottom of the entire report. On the menubar, click on View > Report Header/Footer.
Notice the Report Header and Footer appear on your report.
I’ll place a label and a line in my report header to describe the report…
Notice how it appears once at the top of the report. It is not on the top of page two.
I’ll put a line and a label for a legal disclaimer in the Report Footer.
Notice the report footer is on the bottom of page two.
Now let’s create page numbers for our report. In the Page footer section, place an unbound text box. An unbound text box is just an empty text box from your toolbox with no data in it. Click on the text box tool on your toolbox (not a label).
Drop the text box in your page footer.
Now, you can delete the label that comes with it. Mine says “Text16” and we don’t need it. Now, right-click on the text box and bring up its properties. First, give the box a good name. I’ll call mine PageNumBox. You can name it whatever you want. Now, for the Control Source, type in the following:
This is a special code that says, “put the page number here.” Don’t worry if Access puts square brackets around your code like this: =[Page]. That’s normal.
Preview your report. Notice the little number one in the bottom left corner of the page.
If you want to get fancy, try a little string concatenation like we learned earlier:
=“Page ” & [Page]
Preview your report now and see what happens.
You can even put the total number of pages by adding the following:
=“Page ” & [Page] & “ of ” & [Pages]
Preview now, and see your handy work…
Lesson 5. Miscellaneous Topics
Lets add two buttons to the main menu for our contact table and contact report. We covered this in the last couple of classes, so I won’t duplicate the steps here. Since we can’t open a table directly with the command button wizard, use Miscellaneous > Run Query from the command button wizard prompts.
Select the ContactQ as the source for this button… we can edit and see all of the records from this query (which is just as good as accessing the table).
Put a picture on it, and name it OpenContactQueryButton.
Now make a button for the report. Use Report Operations > Preview Report to open it on the screen.
Select the ContactR as the report to open.
Put a picture on it (your choice) and name it OpenContactReportButton.
Close your toolbox. Save the main menu. Close it, and reopen it. Behold…
You know how sometimes when you hold your mouse over a button in Access you get that little yellow box up that tells you what it is? That’s called a Control Tip. You can create control tips in Access for buttons, text boxes, labels, and other controls. Go into design view and access the properties for your Customer button. Scroll down and find the ControlTip Text property. Change it to “Open Customer Form.”
Click on the next button down (the customer list) and change the ControlTip Text property for that one as well.
You can set ControlTip Texts for all of your buttons. When you’re done, save and reopen your form and notice what happens when you hover over one of your buttons.
Lesson 6. 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
You may want to read these articles from the 599CD News: