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 101
Course Handbook Supplement
By Richard Rost
PO Box 1308, Amherst NY 14226 USA
First Printing 9/13/2004
Copyright 2004 by Amicron Computing
All Rights Reserved
Welcome to the 599CD Microsoft Access 101 Handbook.
This handbook is designed to be a supplement to the full 599CD video course for Microsoft Access 101. 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. Terminology 4
Lesson 3. Planning Your Database 7
Lesson 4. Getting Started 8
Lesson 5. Customer Table, Part One 10
Lesson 6. Customer Table, Part Two 12
Lesson 7. Customer Table, Part Three 13
Lesson 8. Customer Table, Part Four 16
Lesson 9. Entering Data. 19
Lesson 10. Customer Query 23
Lesson 11. Building a Customer Form 37
Lesson 12. Formatting Your Form 57
Lesson 13. Mailing Label Report 84
Lesson 14. Review 95
Lesson 1. Introduction
Welcome to Microsoft Access 101, brought to you by MyOnlineLearningPartner.com and 599CD.com. I am your instructor, Richard Rost.
Objectives for today’s 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.
Pre-Requisites: Windows 101, 102, or 110. Word 101 and Excel 101 are also recommended.
Lesson 2. Terminology
A computer database is a program that lets you store, manage, and manipulate data.
Some examples of databases include Microsoft Access, Microsoft SQL Server, your customer list in Microsoft Excel, a card catalog, a phone book.
Microsoft Access databases are a collection of one or more of the following components: Tables, Queries, Forms, Reports, Pages, Macros, and Modules.
Tables allow us to store data, much like a spreadsheet.
Queries allow us to view data in different ways.
Forms allow us to build a user-friendly interface for our users. Forms also allow us to add functionality to our databases.
Reports allow us to format data to be printed.
Data Access Pages allow us to take data and work with it on a Web page.
Macros allow us to automate repetitive tasks.
Modules allow us to write Visual Basic programming code for our database.
We will work with tables, queries, forms, and reports today. We will cover these last three components in future classes.
Lesson 3. Planning Your Database
Before you start working with Access, sit down with paper or a whiteboard and plan out your database.
Determine what kinds of information you need to work with.
What Tables do you need in your database? What are you storing? Customers, orders, products, vendors, and employees are all examples.
What fields does each table need? A customer table, for example, might contact a customer name, address, phone number, how long they’ve been a customers, whether or not he receives a catalog, etc.
What kinds of queries will you need. If you have 1000 customers in your database, for example, you can make a query to sort them by last name, for example.
What kinds of forms will you need. How user-friendly does your database need to be? What’s the skill level of your average user?
What kinds of reports will you need? Plan them out on paper! It helps to have the previous paper versions on hand (you know, the ones you’ve been using up until now).
The more planning you do before building your database, the easier building your database will be.
Lesson 4. Getting Started
Let’s begin by starting Microsoft Access. Click on Start > All Programs > Microsoft Access.
Access does not start you off in a blank database, unlike Word and Excel that start you off in a blank document and spreadsheet respectively. Let’s create a new, blank database file. Click on Blank Database on the Task Pane under the New section.
Note: if you are using a version of Access before XP, just click on File > New Database.
Access wants you to provide a filename for your database. The New File Database window appears. Let’s type in a filename for this database. I’m creating a database for my fictional computer company PCResale. So I’ll type in PCResale.Net Customer Database. Click Create.
You will now see the blank Microsoft Access Database window.
Lesson 5. Customer Table, Part One
Make sure you have Tables clicked on, and click on New to create a new table.
Click on Design View and then OK.
You will now see a blank table window.
We can now begin entering in the fields for our table. Under the Field Name column, type: FirstName
Do not use a space between “First” and “Name.” I’ll explain why later. For now, do not use spaces in your field names. You can, but trust me, you don’t want to… you’ll thank me later.
I’ve only capitalized “First” and “Name” to make it easier to read.
Lesson 6. Customer Table, Part Two
Hit the TAB key and this moves you to the Data Type column. The default data type is Text. If you click on the drop-down arrow in this column, you’ll see the long list of data types available.
Text is any printable character: a-z, 0-9, and all the other characters like space, @, $, &, and so on. A text field can be up to 255 characters long. A Memo field is a big text field, and can store thousands of characters, but lacks some of the functionality that text fields have. Use sparingly.
Numbers can include counting numbers, integers, decimals, and so on. The Date/Time data type stores dates, times, or both. The Currency data type is a special number that’s optimized for dealing with dollar values.
AutoNumbers are a special kind of number that start at 1 and automatically count up with each number. These are good for customer IDs, for example. Yes/No data type fields are good for tracking true/false data. OLE Objects (Object Linking & Embedding) can be used to store pictures, sound clips, and more. Hyperlinks are used for storing web links. Don’t worry about the Lookup Wizard.
Select Text for the FirstName field, and then press TAB.
Lesson 7. Customer Table, Part Three
The Description column is optional. If you want to store notes here, you can. This information will show up on the status bar in your forms.
Press TAB again, and enter in a new field name: LastName.
Again, select Text for the data type for LastName.
Now, go ahead and enter in the following fields:
· CompanyName (text)
· Phone (text)
· Fax (text)
· Address (text)
· City (text)
· State (text)
· ZIP (text)
· Country (text)
· Notes (memo)
· NumEmployees (number)
· CustomerSince (date/time)
· CreditLimit (currency)
· Active (yes/no)
Sometimes you might have a phone number with letters in it. That’s why we will use a text field for phone. Here’s the test: if you are ever going to be doing calculations on the data, use a number field. If not, use text – it’s easier to deal with. Are you ever going to add up a column of phone numbers? Probably not. Also, some ZIP codes in the US begin with zero. If you use a number field for this, you’ll lose the leading zero. The same problem exists with Social Security Numbers. Use text for these fields.
Notice that with your number types, there is a drop-down option below that let’s you pick what type of number you want. For today, all you have to worry about is Long Integer and Decimal. Long Integers are whole numbers and their negatives. Decimals allow you to store numbers with a fractional component.
Lesson 8. Customer Table, Part Four
We need a way to uniquely identify each customer. None of our existing fields are appropriate, as some may be duplicated (two “John Smith’s” for example.) Let’s add a CustomerID field. Let’s make that an Autonumber field.
I like to have my ID at the top of the table. Let’s move it. Click in the grey box to the left of CustomerID. This will highlight the row.
Now, let go of the mouse button. Click on the same spot again and hold the mouse button down. Drag the row up to the top of the table field list.
This is just a matter of form – it doesn’t change the functionality of the table. Every table should have an ID. A product table should have a ProductID. Employees should have an EmployeeID, and so on.
Let’s now save our table. Click on the Save button.
Let’s type in CustomerT for the table name. Don’t use any spaces. I like to end all of my tables with the letter “T.” This is just something that I do. You can use any name you like, but I recommend that – for now – you stick with my naming conventions. Click OK.
Note: in some books or classes, you may see them name a customer table like this: tblCustomers. That’s fine too. Just pick a naming convention and stick with it. Click OK.
Now you should receive an error message saying that there is no primary key defined. The primary key field is the one field in your table that uniquely identifies each record – in this case the CustomerID. Click Yes and Access will set your CustomerID as your primary key field. Listen to the dialog in the video for a better explanation of primary key fields.
Notice the little key symbol next to CustomerID. This field is now your primary key – no two can be alike.
One of the things that a primary key does for you is that it prevents duplicate values in your table. You wouldn’t want, for example, two customers with the ID number 1827.
Now, go ahead and close your table.
Lesson 9. Entering Data.
Notice in your database window, you now have a CustomerT table showing. Double-click on it to open it.
Your table now opens in data-entry mode.
Hit the TAB key to the FirstName field. Type in your first name. I’ll type in “Richard.” Notice as soon as I do, the CustomerID changes to “1.”
Continue entering in data, hitting the TAB key between fields.
· LastName: Rost
· CompanyName: Amicron
· Phone: 7168374685
For now, enter in your phone number as just a series of digits. We’ll see how to format this in a future class to appear with dashes or parentheses. Continue with your fields:
· Fax: 7168336724
· Address: PO Box 1308
You can resize your columns by clicking and dragging on the border between the column headers (just like in Excel):
Continue entering in your data:
· City: Amherst
· State: NY
· ZIP: 14226
· Country: (leave blank)
· Notes: Such a swell guy
· NumEmployees: 10
· CustomerSince: 1/1/94
Note: If you enter in a two-digit year, anything from 00 to 29 will become 2000 to 2029. Anything from 30 to 99 will become 1930 to 1999.
· CreditLimit: $500.00
Active is a check box. You can either use the mouse to check it on or off, or use the spacebar.
Hitting TAB again will bring you down to the next record.
Go ahead and enter in four or five more customers. The data itself isn’t important. Enter in whatever you’d like. Just make sure you enter in customers from a few different states and a few different countries. You can leave some fields – like phone numbers – blank, if you want.
If you decide you want to delete a record, just click on the box to the left of the row, and hit Delete on your keyboard. Say yes when asked if you’re sure you want to delete the record.
Go ahead now and close your table down. You may be asked to save changes to the layout of your table. This is because you resized a column or two. Say yes and your table will be saved.
Lesson 10. Customer Query
We only have a few customers in our database now, but let’s pretend we have thousands of customers, and we want to sort the list and add some other criteria – like only showing customers from NY. Click on Queries and then New.
The New Query window appears. Select Design View and click OK.
The Query Design window appears and the Show Table window appears in front of it. Access wants you to select a table to go into your query. Select CustomerT (the only table we have right now) and click Add. You will notice a mini CustomerT window will appears behind the show table window.
Click on Close to close the Show Table window. You are now in the Query Design window.
Click and drag the FirstName field down into your query.
Notice the field now appears in your query.
Do the same now for the LastName field.
Click on the Run button to run the query.
The query has now “run.” You can see the data you requested.
Let’s go back to Design View. Click on the View button.
Click in the Sort row under the FirstName column. Select Ascending from the list of choices.
Now run your query. Notice the records are sorted by FirstName in ascending order.
Return to design view. Click in the Sort row under the LastName column. Change it to Ascending.
Save and run your query.
Notice that this isn’t exactly what we were looking for. We’re still sorted by first name. Return to design view. The key to remember here is that Access sorts the fields left to right. If you want to sort by last name then by first name, you have to move the LastName field to the left of the RightName field. Here’s how you do it. First move your mouse over the column, right on the little gray bar.
Now, click on that bar. Notice the column is highlighted.
Now, let go of the mouse button. Click on that same spot, and drag the column to the left. You’ll see the mouse “carrying” a little box with it. Let it go to the left of the FirstName column.
And now you should notice the column has moved.
Now, run the query, and you should see what we were after in the first place.
Now the boss wants to only see customers from New York. To do this, we need to add the State field to our query. Find the State field in your table field list, and double-click on it to add it to the query.
Now run your query, and you will see all of the customers with their states.
Return to design view. To limit the customers only from NY, click in the criteria row under the State column. Type in “NY” and hit TAB. You don’t need to type in the quotes – Access will put them there for you (although it is a good idea to get in the habit of typing them).
Go ahead and run your query, and now you can see that only customers that match our criteria (those customers from NY) are showing up.
Let’s go ahead and save our query now. Click on the Save button.
I’m going to call my query CustomersFromNYQ. Notice I’ve used no spaces, and I put a “Q” at the end. Again, this is just my personal style for naming my objects.
My query is now saved. Notice the title at the top of the query window.
Let’s close this query now, and return to the main database window. Notice that we have one query showing up in our Queries section.
The beauty of a query is that now all you have to do is double-click on the query to run it again. The work is already done. Next month, when the boss wants this same report again, it’s there. Also, a user who doesn’t know how to build queries can run this query just by double-clicking on it.
Lesson 11. Building a Customer Form
Forms are objects for working with your data on the screen in a nice pretty, user-friendly screen. This takes practice! You’re not going to have great-looking forms after one lesson. Practice makes perfect. Begin by clicking on Forms from the database window.
Now click on New on the top toolbar.
There are a lot of different ways to make forms. We’ll go over some of these other options in future classes. For today, click on Design View.
Next we have to tell the form where to get its data. Drop down the box at the bottom of the form and select CustomerT from the list. Click OK.
You should now see a blank form (Form1) along with a toolbox and a field list.
For today, we’re not going to use the Toolbox. Let’s go ahead and close it.
Let’s move the Field List over to the side. Click and drag it’s titlebar and move it over.
Note: if you don’t see the Field List, don’t panic. Look on your toolbar for the following button. Just click on this button to open and close the Field List.
Now, let’s move the fields we want from the Field List onto our Form. Click on the CustomerID field.
Now, click and drag the field from the Field List over to the Form.
When you release the mouse button, the field appears. Notice you have two objects. The first is a Label that tells the user what field this is. The second is a Text Box that will actually display the data.
To see the form “in action” click on the View button on the toolbar. This will take you to “form view” mode where you will see the form as the user sees it – with the data in it.
Notice you can now see the form with the one field we placed on it, and the data from that record.
Now, click on the T-square button to return to design view (just like with queries).
Now, let’s drag over another field. Click and drag the FirstName field. Here’s a tip: when you’re lining up your fields, don’t line the little tiny box that you’re dragging up with the label. Line it up with the text box from the previous field, like this:
If you get it just right, your fields will be perfectly lined up – one beneath the other.
Again, let’s go back to Form View. Notice the additional field with its data.
At the bottom of the window, you will see little Navigation Buttons that you can use to move between the different records. There is a button to move to the next record, previous record, first record, last record, and a new blank record.
Click on the Next Record button and Access takes you to record #2.
Click on the New Record button and you’re taken to a blank new record. You will see (AutoNumber) in the CustomerID. As soon as you enter in a FirstName, this number will be assigned – just as if you were entering information directly into the table.
Remember, this is Live Data! If you add records, make changes to existing records, or delete records, you’re changing the data in the table. This is not a copy of the data. Let’s return to design view now and add the LastName field to our form.
Oops! I missed. I wasn’t quite perfect with my dragging and I dropped it in the wrong place. How do we move it? Notice how LastName is highlighted (see the dots around it). If it’s not highlighted, then just click once on it. Now, move your mouse over it until you see a hand like this:
Now, click and drag to move the field wherever you want it.
Alternatively, you can also click and drag the field in one motion, provided it was not highlighted to begin with. Just click off in “right field” somewhere to unhighlight all of the fields, and then click and drag one of the fields.
Continue practicing by bringing the fields into the form one at a time.
Yes, as a shortcut, you can bring them all over at once. To show you this, let’s first remove all of the current fields from our form. To remove one field, you can just click on it and press DELETE on your keyboard. To remove them all, draw a box around all of them…
This will select them all…
Now hit DELETE on your keyboard.
To bring all of the fields over at once, double-click on the Title Bar of the field list box. This will highlight all of the fields.
Now, click on any one of them and drag it over to the form. Notice how the little box is different because you’re dragging multiple fields.
When you release the mouse button, all of the fields are placed.
One mistake that people commonly make is that they bring their fields over too close to the edge of the form…
And then their labels are all squished up against the side…
Make sure when you’re bringing your fields over, you don’t go any farther over than, say, the 1-inch marker on the rulerbar. You can also drop them over at, say, the 1.5-inch mark and then while they’re all still highlighted, use your Hand tool and drag them all over so they look nice.
Let’s go ahead and close the field list.
If you scroll down the form, you’ll see all of your fields. Notice that Notes came in as a larger text box because it’s a memo field. Also notice that Active is a check box.
Let’s save our form. Click on the floppy disk Save button. I’ll call my form CustomerF and click OK.
Go to Form View and you’ll now see all of your fields on the form.
Close the form and return to the database window. Notice your form shows up in the Forms section.
Lesson 12. Formatting Your Form
Let’s open up our customer form from the last lesson.
Go to design view.
First, let’s change our background color. Click somewhere on the background of the form. You will notice the horizontal band that says “Detail” will become highlighted.
Notice on the toolbar there is a button that looks like a paint can. This is the background / fill color button. If you click on it now, you get the color that’s under the paint can (dark gray right now).
If you click on the down-arrow next to the paint can, you can select any color from the color palette.
I’ll select a light blue color.
Click on the label for the LastName field.
Now delete it. Hit DELETE on your keyboard.
Using your Hand tool, drag the LastName textbox up next to the FirstName textbox.
Let’s now change the text in the label. Click on the FirstName label once to select it.
Click on the label a second time to edit the text in that label. Now you can type in whatever you want for the label (remember this is what shows up on the form).
I’m going to type in Name (F,L):. I think most people can figure out that this means first name, last name.
Switch to Form View and see what your form looks like now.
Return to design view. Move your CompanyName field up.
Edit the label for CompanyName and put a space between “Company” and “Name.” Remember, this is what the user sees – it’s a label – you can break the rules for labels to make them look good.
Let’s make the CompanyName field wider. Click on the field.
Move your mouse over the little box on the right side of the field. Notice your mouse pointer changes to a double-arrow.
Click and drag on this box…
Notice that the text box is now resized.
Go to Form View and notice how it is resized.
Move the Phone field up. Delete the label for the Fax field. Slide the Fax field up next to the Phone field. Finally, edit the label to reflect the change…
Slide the Address label up and make it bigger.
Let’s move City, State, ZIP, and Country all up at the same time. Draw a box around all four of them. Note that your box really just needs to touch them – it doesn’t have to completely enclose them.
Using the Hand tool, drag them up in place.
Delete the labels for State and ZIP code.
Slide up the State and ZIP fields, and resize them accordingly as shown.
Edit the label…
Slide Country up and resize it.
Let’s drag the Notes field up to the top, right side of the form. Notice if you drag the Notes field too far to the right, it will actually make the Form itself wider.
You can make the form wider by clicking on the border of the form and dragging it manually.
Let’s move the Notes label over the textbox. Move your mouse over the large box in the upper-left corner of the Notes label. Notice you now have a single finger.
Now, click and drag using the finger tool to move the label above the textbox. The finger tool moves a label or textbox independent of the other, whereas the hand tool moves them both together.
Notice what we have done…
Now you can use the Hand tool to move them both…
Let’s resize our Notes box to make it wider and taller. Note that you can use the other boxes (like the ones in the corners or the bottom middle) to resize the text box in different directions.
Now, using our Paint Can (fill tool) let’s select a light yellow to change the background color of this field.
Notice our Notes field is now light yellow.
Notice that there are tools similar to the fill color tool that will let you change other attributes of the box. For example, there is a font/fore color tool that changes the color of the text. This one, for example will change the text to Red.
You can drop the color palette down and select a different color, like blue.
You can use this box to change the border color…
This box changes the line width, the thickness of the box border…
From the next box I’m going to select the Shadowed special effect.
My notes box is now shadowed…
Now let’s move the rest of the fields up. Highlight all of the fields on the bottom of the form…
Using the Hand tool, move them up…
Let’s move the Active checkbox over to the right to save space.
Now you can take the bottom of the form and resize it – to make the form shorter and save even more space.
Save your form. You won’t be prompted for a name because you’ve already saved your form with a name. Nothing will appear to happen – but your form is saved.
Now, take a look at your form in Form View.
To add a new record, simply click on the Add New button.
This will bring you to a new blank form.
You can type in your new record – notice the (AutoNumber) will be replaced with the next counter number once you enter in your first bit of data.
To delete a record, click on the big vertical gray bar to the left of the form. This will select the entire record.
Now hit Delete on your keyboard. You will be asked if you’re sure you wish to delete the record. Answer Yes and it’s gone – note that you cannot undo this delete.
Lesson 13. Mailing Label Report
We’re going to create customer mailing labels using the Reports feature of Access. Click on Reports, then New.
Select the Label Wizard from the list of options. Then, from the drop-down menu, select CustomerT as the source of your data.
The Report Wizard starts. You’re first asked to select the type and style of labels you want. We want Avery 5160 labels. This is just the type of label that I’ve chosen to use – it’s a very common label style with 3 columns of 10 rows of labels (30 per page) – standard address labels. Click Next.
Next you’re asked what kind of font you want for your labels. Arial, 8 point, black is fine with me. Just click Next.
Next we have to set up our Prototype label. This just means we need to lay the fields out on one master label, and Access will make all of the rest of them for us. Begin by bringing over the fields from the left that we want on our label – in the order we want them. Make it look like a regular address label. Start by clicking on the FirstName field and then click on the little right-arrow button.
Now notice that after you click on the button, the field is brought over onto the prototype label.
Now, you would want a SPACE between then FirstName and LastName, so hit the SPACEBAR on your keyboard.
Now bring over the LastName field. Then, press ENTER on your keyboard to move down to the next line.
Now bring over CompanyName and press ENTER.
Same thing for Address…
Now bring over City, press SPACEBAR, State, SPACEBAR, ZIP, SPACEBAR, and optionally Country.
If you want to insert a comma or any other characters in your prototype label (like a comma between city and state), you can. Click Next.
Now you’re asked if you want to sort by any particular fields. Let’s sort our results by ZIP code. Click on ZIP code and then click on the single-right-arrow button. Click Next.
You can also sort by Address (if the Zip codes are the same, they’ll next be sorted by Address). Click Next.
Let’s give this report a good name, like MailingLabelsR. Click Finish.
Our mailing labels are finished. They will appear on the screen.
Notice your mouse has turned into magnifying glass. You can click to zoom in and out.
There are navigation buttons on the bottom of the form so you can move between the pages (if you have more than one page of 30 labels – which we do not).
There is a drop-down box that will also let you zoom in or out.
You can also click on the “OfficeLinks” button to export your report to Word or Excel.
When you’re ready to print, just click on the Print button.
Again, you’ll notice your mailing labels are listed in the Reports section. To open the report again, just double-click on it.
Lesson 14. 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: