Lesson 7: Introduction to the Access Database
Keeping student records
7.1 AN OVERVIEW OF THE DATABASE CONCEPT
The database component of MS Office is designed to act as an "intelligent"1 electronic filing cabinet. Like a manual filing cabinet, it enables you to
• collect sets of related data and keep the data organized;
• update the data once you've collected them, by adding or deleting records and by changing existing records;
An electronic filing cabinet such as a database allows you also to easily and quickly use the data sort them, create reports, merge the data with other documents, and so forth.
How is a database organized?
Access is an object-oriented relational database management system. The objects that make up any particular database that you create using Access are illustrated in Fig. 7.1.
Fig. 7.1 The Access database objects
1 The word "intelligent" is enclosed in quotes so as not to create a false impression. With regard to computers, the word is overused, and strictly speaking inappropriate. It also can be misleading. The only "intelligence" exhibited by these otherwise "dumb" machines has been programmed into them by intelligent human beings.
• A Table is an object which represents the data in rows and columns, rather like a
spreadsheet. For this reason, when you view a table in an Access database, you are in
what Access refers to as a Datasheet View. You will learn about Tables in this lesson.
• A Query is an object which makes a request to the database to find some set of data that
is stored in the database. You will learn about queries in Lesson 8.
• A Form is an object which gives the user another view of the data in the database.
Whereas a Table allows the user to view multiples records at once, a Form displays the
contents of just one record at a time. You will learn about Forms in this lesson, too.
• A Report is an object which is designed based on the data in the database and which is
used to inform the user of the selected contents of the database. You will learn about
reports in Lesson 8.
• A Page is a special type of web page designed for viewing and working with data from
the Internet. This Access feature is beyond the scope of these tutorials.
• A Macro is a sequence of instructions which can be carried out with a single click of the mouse button on a button in a toolbar or by pressing a key or combination of keys on the keyboard. You may recall developing a Word macro in Lesson 6.
• Finally, a Module is a collection of programming procedures designed to give
programmer control over the Access database look and feel.
A reminder of the general terminology for secondary storage of data
Words are our stepping stones to knowledge. Personal computers today, for the most part, still use electronic disks as the medium for the secondary storage of data. The Windows computer operating system, like most operating systems, organizes data hierarchically on the disk using documents and directories. Documents are usually stored in directories or subdirectories that are part of the overall directory file management system.
Within documents, different terminology is used to describe the organization of data
depending on the type of document in question.
• Word processed documents tend to organize data in paragraphs and sentences;
• spreadsheet documents organize the data in rows and columns;
• database documents organize the data into records and fields.
The Access database terminology in particular
An Access database is able to contain thousands of records; records contain fields; each field contains the raw data called entries made up of characters which are the basic building blocks of data.
For example, let us say you have given a student an assignment to study birds and to build a database to store the data collected. The student decides to set up a record for each bird sighted.
Each record will include field names (Access database field names may be up to 63
characters long) such as Common Name of bird, type (Family) of bird, Size, Habitat, etc. A field name such as Common Name of bird might contain the entry "seagull", for example.
A database thus contains records; records have fields; the fields contain entries. These entries are made accessible by the Data View (Table), Form, and Report functions of the Access database. At the end of the year, for example, the student would be able to write a sophisticated report using the word processor, merge into the report the data collected in the database, and even add illustrations of the birds.
It will be useful for you at this stage to see an actual Access database on the computer.
Put your Work Disk in the disk drive, then open the Office Database Management program called Access
In the New File Task Pane (Fig. 7.2), under Open a file, click on More files…
Fig. 7.2 The New File Task Pane
Access will display the Open dialog box.
Make sure the drive with your Access Disk in it is the active drive, then double click to open the database document named Birds (Birds.mdb)
Showing the Full menus and organizing the Toolbars
In case you're using a computer in a lab or some computer other than your own, you should set the options to Always show full menus, Show the Standard and formatting toolbars on two rows, List font names in their own font, and Show ScreenTips on toolbars.
You may recall doing this at the beginning of all the previous lessons. If the computer you're using doesn't already have these settings, here's what you do.
In the Tools menu, select Customize…, and in the dialog box that pops up, select the Options tab (Fig. 7.3)
Fig. 7.3 The Customize dialog box
Make sure there is a check mark next to the item to Always show full menus
While you're at it, check the box next to Show Standard and Formatting toolbars
on two rows, List font names in their font, and Show ScreenTips on toolbars
Click the Close button when you're ready
Access displays a dialog box (Fig. 7.4) prompting you to decide how you want to view the data on the screen.
Fig. 7.4 How do you want to view the data in the database?
Each of the Objects listed in the menu on the left side of the Database dialog box represent one of the objects of the Access object-oriented database management system.
You can view the data either as a form, record by record, as illustrated in Fig. 7.5.
This Forms view gives you a view of the records one by one. On the other hand, you can view the data as a Table, where the data is displayed in columns or as a list (Fig. 7.6).
Fig. 7.6 Records listed in Table View
Make sure the Tables Object button is selected on the Birds Database dialog box and the BirdSightings table highlighted, then click on the Open button in the Birds database toolbar (or just double click on BirdSightings)
In the top left corner of the window, in the Title bar, you see the Table name (BirdSightings). In the bottom left corner of the Form and Table View windows are tools to access specific records in the database.
Fig. 7.7 illustrates what each of these navigation tools does.
Fig. 7.7 Navigation tools in the Access database
As you can see there are 50 records in the database. The field names (Common Name, Family, Size, etc.) are at the top of each column of data. As you can see, the screen is not big enough to allow you simultaneously to see all the records in the Birds database. If you want to scroll through the records or look at any specific record, you have several options:
You can use the scroll bar on the right of the screen to scroll through the records.
Try this now
To move one by one through the records, you can click left or right on the arrows in the access control tools in the lower left corner of the window.
Try this now
To go directly to a specific record, you can select the number of the record you want to view by typing the number of that record in the small data entry box between the arrows.
Try this now to select record 12, and hit Enter
You can select Go To in the Edit menu and go to either the First, Last, Next, or Previous record, or you can opt to create a new record. So much for the nuts and bolts of databases. You are now ready to get your feet wet planning your own database of student records.
In the File menu select Close to close the BirdSightings table, then in the File menu select Close again to close the Birds database
Eject your Work Disk from the disk drive
7.2 PLANNING A NEW DATABASE
Before we begin to develop a new database, you need to have at hand the disk on which you're going to save it. You may be using a CD-RW or a zip disk for these tutorials, in which case make sure it is in the disk drive. In Lesson 1, you began by preparing several blank, formatted 1.44 mbyte floppy disks, one of which you named Access Disk. So, if you are using floppy disks, make sure you have this disk ready now to save the documents you will create in Lessons 7 and 8. Also, if you didn't already do so, prepare a second blank, formatted disk for backup purposes. Call this disk Access Disk Backup.
There are three rules of database design: Rule 1—Plan; Rule 2—Plan; Rule 3—Plan.
The more complex the data, the more you need to plan. But even the simplest database
should be thought through on paper before being created in Access. Poor planning often results in a database that fails to meet overlooked needs.
Points to ponder when planning a database
The rule of thumb that should guide you is that changes become increasingly difficult to make the further along you go in the process of building a new database. This is true of anything you create. Here, then, are some maxims that you should bear in mind when designing a database.
Taking your time up front saves time later on
The database you create will have a long, useful life if you take time to plan it carefully. After you have decided on the fields to include with each record, and before you create the database, you should still invest time designing layouts for reports. Thinking about reports will cause you to think about what data you plan to put in the database.
Teamwork helps
During the planning stage, run your ideas by others who are familiar with the kind of database you have in mind. Network among your friends. Tell them what you have in mind. Ask them to review your design. You'll be surprised how many valuable ideas they'll come up with that may have escaped you if you had relied on your own resources. Another good idea is to involve your students in the design. This will help them learn skills that will benefit them throughout their lives.
Keep fields simple
The more "atomic" your fields the more flexible will be your database. Atomic here means "reduced to its simplest form." The less each field captures in the way of entries the better. For example, in a database of names and addresses, you would keep each part of the person's name as a separate field. The first name should be stored by itself; the same for the middle name and last name. That way you can sort the names by first or last name. You can print a listing last name first or first name last, with or without the middle name, and so on. You will have choices. Lumping the whole name under one field limits your options.
Design guidelines for a Student Roster database
You have to build a database for a Student Roster. Let us say that your planning has helped you decide the following about the database and its use:
• The database will be accessible to, and managed by, your students. Each of them will enter their own data at the beginning of the year. You will advise them that they are not obliged to fill out every field--that it is OK to leave entries blank. Privacy is an important issue to which our students need to be sensitized. We need to take every opportunity to teach them that they should exercise control over data about themselves. They must make decisions about what is, and is not, privileged information. In a world where, inevitably and increasingly, personal data will be available to whomever wants to use it, our students must learn early on in their lives that they have a responsibility to keep tabs on that data so as to ensure, as far as is humanly possible, that they are correct at all times. Managing their own records on the class database will give them valuable experience in this privacy arena.
• You have drawn up a list of fields (illustrated later in Table 7.1) for each record in the
database. Don't start entering them in yet; you still have a couple of things to get clear at
this planning stage.
• All the fields will be treated as simple text, except the Date of Birth field, which will be of Date type, and the Brothers and Sisters fields, which will be of Number type. When you declare a field as Date type you can later sort the records in the database chronologically, which has its uses. Likewise, if you declare fields as a Number type you can later sort them numerically. You can also manipulate numeric data mathematically, creating totals, averages, and so forth.
• In your class you will designate one student per week as the database manager. A schedule will be drawn up for this purpose at the beginning of the year. As far as possible, any interaction with the database, including the teacher's interaction, will be monitored by that student unless this is inappropriate for reasons of privacy. Other students may use the database only when the student manager is present.
• The database manager will check the database for items of interest (birthdays, etc.) for the week he or she is in charge, and will interact with the rest of the class to decide how to recognize such items of interest.
• The last activity on the database at the end of each week will be the process of making a backup of the database. This will be necessary because changes to data can occur at any
time (a student has a new brother or sister, for example). This would be done by the student database manager under the teacher's supervision.
You could add other features in an actual working environment. With all this in mind, you are ready to create the database which will hold your student records.
7.3 SOME HINTS BEFORE YOU CREATE THE DATABASE TEMPLATE
You are going to start a new document for a database of Student Records. You want to save all your personal documents on your own data disks (not on the Work Disk).
Put your Access Disk in the disk drive, then from the File menu select New...
Access now presents you with the New File Task Pane as before (Fig. 7.8).
Fig. 7.8 New File Task Pane
In the Task Pane, click on Blank Database to open a new database, then in the File New Database dialog box (Fig. 7.9) make sure the drive in which you have your Data Disk is the current drive
Fig. 7.9 The File New Database dialog box
Click on the New Folder icon in the File New Database dialog box, name the new folder Database Documents and click on OK
Type Template for the document name and click on the Create button
The system knows that this is a new document. This is why you are prompted to begin by
creating (defining) a new Table for the database. Notice that the Table object is selected by default on the left side of the dialog box. If you look towards the top of the dialog box, you can see buttons for Open, Design, and New (Fig. 7.10).
Fig. 7.10 Object control buttons
Click on the New button to create a new Table in the Template database Access now displays the New Table dialog box asking you to decide how you want to build the
database table (Fig. 7.11).
Fig. 7.11 New Table dialog box
If you select the Datasheet View you will be presented with an empty list of 30 rows and 20 columns as a basis for you to design your new layout. If you select Design View, on the other hand, you can go ahead and enter all the fields for the database and specify the data types at the same time. This is what you are going to do in order to learn about data types and data formats at the same time as you build the database..
Select Design View in the New Table dialog box and click on OK
You will now see on the screen the Table Design View dialog box (Fig. 7.12).
Fig. 7.12 The Table Design View dialog box
The field names for the new database
On the screen Access is waiting for you to begin typing in the field names. The cursor is located in the first data entry box in the Field Name column, prompting you to type in a name for your first field. Access field names can be up to 64 characters in Field Size. Before you start entering the field names, however, carefully read what follows so you will have fewer surprises as you go along. Refer back to this section if you need help with any of the specific tasks that follow.
Editing field names You can change the field names after you have committed yourself to
them. You can also easily add or clear fields. So at this stage do not worry about making
mistakes. If you start typing in a name and realize you have made a mistake before you
go on, just use the Backspace key to back up and try again.
Selecting the data type of a field and adding it to the database As illustrated in Fig. 7.12
above, after typing in the field name in the first column, decide what the field's data type
should be in the second column.
Setting up predefined entries for a field To reduce the likelihood of bad data getting into
the database, you can limit the user's choices when they enter the data for a particular
field. For example, when you enter the field Gender, you can limit the choices to M or F
and you, or any other user, can simply pick one or the other from a list assigned to the
Gender field. In our database, you'll be predefining the City and State fields as well.
7.4 CREATING THE DATABASE TEMPLATE
Entering field names for the database
Table 7.1 contains the complete list of field names for the Student Roster database. You'll be referring to this Table frequently as you work your way through the next several pages, so you might like to mark this page so you can find it easily when you need to.
Type only the Field Names that appear below in the first column of Table 7.1. The second column tell you what Data Type to choose. The other columns in the table below (Required, Field Size and Comments columns) simply explain whether data is to be "Required" for that field from the user and what size (in text characters) the field is allowed to be, along with tips on what to do.
In the first data entry box in the Field Name column, type the field name First Name and hit Tab to move the cursor over into the Data Type column Notice, in the lower right section of the window, that Access provides help to guide you as you enter the data for each part of each field. Read this help as you go along.
In the lower left corner of the window you can see the Field Properties dialog box (Fig.
7.13). In Table 7.1 above, the Field Size column specifies the number of characters required for several of the fields in your database.
Check this now so that you know the correct size for each field.
Now, look down at the lower left corner of the window where you can see a list of Field Properties, along with data entry boxes to specify Field Size, Format, and so forth
In the Field Size box type the number 10 (10 characters)
Fig. 7.13 The Field Properties dialog box
For the First Name field, and for several other fields (again check out Table 7.1) you want the user to definitely fill in an entry—in other words, an entry is required. You don’t want the field left blank. Here’s how you can ensure that this happens in your database.
In the Field Properties area, double click in the data entry box next to Required (Fig. 7.13) in order to toggle (switch) the entry from No to Yes
Since you are requiring a First Name, you don't want it to be zero Field Size, so, in the Field Properties area, make sure the Allow Zero Field Size entry is set to No
Now go back up and click in the next Field Name box below First Name so you can enter more database fields
Enter the field names Middle Name (10 characters), Last Name (15 characters), Home Contact (20 characters), making sure you have Yes in the Required Field Property and No in the Allow Zero Field Size Field Property for each of these fields
Now enter the field name Nick Name (10 characters), but leave No in the Required Field Property box
Checking or entering data automatically
Using the Lookup Wizard to specify selected values for a field For fields such as the Gender, City, and State fields, next in the list of Field Names (see Table 7.1—page 204), the values that should be entered are predictable. For the Gender, it will be M or F; for the City it will be a couple of towns or cities from which your students come; for the State it will be the abbreviation for the state in which you live.
The next series of steps show you how to specify selected values such as this for a field.
Enter the field name Gender in the next Field Name box and hit Tab
Click on the small triangle (the down arrow) in the Data Type column and select Lookup Wizard… in the drop down list
In the Lookup Wizard dialog box (Fig. 7.14) click on the radio button next to “I will type in the values that I want”, then click on Next>
Fig. 7.14 Opting to enter the values yourself
In the Lookup Wizard (Fig. 7.15), leave the default 1 for the number of columns, hit Tab or click in the data entry box under Col1 and type M
Fig. 7.15 Entering the values for the Gender field
Hit Tab or click in the next data entry box in the Col1 column, and type F, then click on Next> to go to the next step in the Wizard
There’s no need to change the label for the Lookup column (Gender), so click on Finish in the dialog box
Go back up and click in the data entry box under Gender, type the field name Address Line 1, hit Tab, leave the Field Size as the default of 50 and make it a Required field
You should use the Lookup Wizard to define a set of data if your students all come from
the same one or two towns, a likely scenario in any school district. If you teach in a public school in the US, your students will also all live in the same State. So for the City and State fields you’ll use the same steps as you did to create the Gender field.
Click under Address Line 1, type the field name City, hit Tab, then select Lookup Wizard… for the Data Type and follow the same steps as you just did for the Gender field to enter the names of a couple of your local cities or towns, leave the Field Size as the default of 50 and make it a Required field Create the State field with the Lookup Wizard Data type, too, and enter the initials for your state as the value for the list, leave the Field Size as the default of 50 and make it a Required field
You will see later that the city or cities you name, along with the State, automatically will appear as selections when entering data into those fields in the database.
Using an input mask to control the format for a field
Certain fields always have the same format, like zip codes or phone numbers. You can have Access help you control the way the user enters data for such fields by using an input mask, which forces the user to enter the data a certain way. This reduces the likelihood to some degree of incorrect data getting into the database. Let’s see how this works with the Zip code field, next in the list of Field Names (see Table 7.1).
Back up in the list of field names, click under State, type the field name Zip Code, then hit Tab
For the Zip Code you should use a Field Property Input Mask to help the user enter the zip code in the correct format.
In the Field Properties dialog box in the lower left corner of the window, click
in the Input Mask box (Fig. 7.16)
Fig. 7.16 Using an Input Mask
Next, click on the ellipses (...) that pop up at the end of the data entry box (see Fig. 7.16)
Click on Yes to Save the Table when Access prompts you to do so, then click on OK to accept Table1 as the default name for the table
Primary key or no primary key? that is the question
Now you are asked to define a Primary Key for the Table. If you were designing the database for an administration application in the Principal’s or Superintendent’s office, this would be an important consideration, because your database would be large, and there would be many ways of looking at the database (called "views" on the data) represented by different tables. The purpose of a primary key is to allow for easy manipulation of the data across multiple tables. This is not something the class teacher needs to worry about, so you won't need a Primary Key for the table.
Click on No to tell Access you don't want a Primary Key field in the database
Now click on the Zip Code input mask in the Input Mask Wizard dialog box (Fig. 7.17), click on Try it to see how the mask will look and enter a zip code with more than 9 digits (the system will beep if you try to do so)
Fig. 7.17 Zip Code input mask
Click on Next> to proceed to the next step in the Wizard
The Wizard now asks you in a new dialog box (Fig. 7.18) if you want to change the input mask for the Zip Code and if you want to select a placeholder character which will guide the user when they eventually enter zip codes into the database.
Fig. 7.18 Option to change Zip Code input mask
The underline character is the default, as you can see, so select it as the placeholder character for the Zip Code field, then click on Next> to proceed to the next step in the Wizard
Click in the radio button to select With the symbols in the mask, like this:, then click on Next> to proceed to the next step in the Wizard, and click on Finish
Make it a Required field
You need to follow the same steps to use an input mask for the Phone Number field. Remember to select Text as the data type for the Phone Number. If it is entered as Number type it will be treated as math (numeric) and the hyphen will act as a math operator. In any case, you can only use an input mask with Text or Date/Time data types.
Type the field name Phone Number then click in the Input mask Field Properties data entry box
Now click on the ellipses (...) that pop up at the end of the data entry box
Click on Yes to Save the Table when Access prompts you to do so
Next click on the Phone Number input mask in the Input Mask Wizard dialog box (Fig. 7.17) and click on Try it to see how the mask will look
The Wizard now again asks you in a new dialog box if you want to change the input mask for the Phone Number and if you want to select a placeholder character which will guide the user when they eventually enter phone numbers into the database.
Click on the underline character to select it as the placeholder character for the Phone Number field
Click on Next> to proceed to the next step in the Wizard, then click in the radio button to select With the symbols in the mask, like this:
Click on Next> to proceed to the next step in the Wizard, and click on Finish Make it a
Required field
For the Date of Birth field, which is next in the listing in Table 7.1 (page 204), you want to select Short Date as the data type for the field, and apply an input mask to the data entry for it, too.
Follow the same steps as you just did for the Zip Code and Phone Number fields, except this time select the Short Date input mask in the Input Mask Wizard dialog box
Make it a Required field
Completing definition of the database fields
The remaining fields in the database (Clubs, Hobbies, Favorite Sports, and Favorite Subjects) will all be Memo data type to allow the user to type a longer entry if desired.
When you have completed the entries for all 19 field names click on Close in the window control menu (the little grid-like icon in the top left corner of the Design View window next to Table1)
Click on Yes to tell Access to save the changes you have made to the Table (Table1)
You are now back to the top level dialog box for the Template database. You have defined the database Table which will allow you to start entering the data for a class of students.
7.5 COMPLETING PREPARATION OF THE DATABASE TEMPLATE
Creating a Form for the new database
The following exercises will help you learn how to prepare a database Form.
A Form is a database object which allows you to view the data one record at a time. This
makes a form a convenient tool for data entry purposes because it allows the user to see all the fields in a record at one time, unlike the Table view (the Datasheet View) which will often be unable to show all the columns of data across the screen.
On the left hand side of the Template dialog box, click on the Forms Object button
You now have a choice of creating the new form in Design View, which means you'll have to do all the work yourself, or you can use the Form Wizard, which is a lot easier.
Double click on Create Form by using Wizard to bring up the Form Wizard dialog box
The Form Wizard (Fig. 7.19) is displayed on the screen.
Fig. 7.19 The Form Wizard dialog box
The Wizard will help you design the Form based on the fields and data types (including input masks) you have selected. You'll begin by telling Access to include all the Available Fields for the Form.
Double click on each field one by one in the Available Fields: list in order to transfer all 19 of them across to the Selected Fields: scroll box
Once you have selected all 19 fields (there should be none left in the left hand side of the dialog box), click on Next>
The Form Wizard now asks what layout you want for the new Form (Fig. 7.20).
Fig. 7.20 The Form layout options dialog box
Columnar layout will work well for our purposes, and that is the default. But check out the sample layouts while you are here by clicking on the radio buttons.
End up with the Columnar radio button selected, then click on Next> again
Now the Form Wizard asks you to choose a style or look for the Form (Fig. 7.21).
Fig. 7.21 The Form style options dialog box
Click on each of the styles so you can see what options you have, but for this exercise make sure you end up with the International style selected, then click on Next> again
In the next dialog box (Fig. 7.22), the Wizard asks you to name the new Form (Form1 will do fine) and also whether you want to modify the design in any way.
Fig. 7.22 Option to modify the form
In the dialog box for the next step in the Form Wizard, change Table1 to Form1
Make sure the radio button next to Modify the form’s design. is selected, then click on Finish
Access now displays the Wizard’s default layout for the Form (Fig. 7.23).
Fig. 7.23 The basic Form designed by the Wizard
If necessary, either move or close the floating Form Design Toolbox out of the way so it’s not on top of your
Fig. 7.24 shows the Form after you have rearranged to field names and data entry boxes.
Fig. 7.24 The new database Form after rearrangement of field names and data entry boxes
Rearranging the layout of a record in the Form window
You need to make a few adjustments to the basic Form so that there is enough room for the field names where they are too long to fit in the space allowed by the Form Wizard. You also need to reposition some of the fields to make it easier for the user to enter data into the various data entry boxes. In the exercise that follows, use the dots in the grid to help you align the data entry boxes for each field in the form.
Let's start in the lower right corner of the Form, with the Favorite Subjects field. Fig. 7.25
illustrates how the Favorite Subjects data box will look when you're done adjusting its size.
Fig. 7.25 Adjusting the size of a data entry box in a Form
First click on the Favorite Subjects data box to get the handles around it
Now grab the handle at the bottom center of the box and drag up to make the box about 1/3 smaller (you don’t need to be too precise about this)
Next grab one of the handles on the right side of the box and reduce the width of the box by about 1/3
Now position the mouse arrow along the lower edge of the box (but not on one of the handles) till it changes into a small black hand, then hold down the mouse button and drag the Favorite Subjects box, along with its Field Name, back down towards the lower right corner of the Form, and tuck it there, as illustrated in Fig. 7.24
This is awkward at first, but you’ll get used to it.
Do the same to the Favorite Sports and Hobbies data entry boxes, re-sizing them so they’re more or less the same size as the Favorite Subjects box, then move them down and to the right
Next, reduce the Clubs data entry box so it is the same as the others you just changed, then drag the box up and to the right and place it above the Hobbies data entry box
If you did the job correctly, your Memo data entry boxes should all be lined up as illustrated in Fig. 7.26.
Fig. 7.26 The lined up Memo data entry boxes
Now click on the blue Field Name Favorite Subjects (you can only see “Favorite S”), grab one of the handles on the left and drag the box to the left so it becomes wide enough to show the whole field name
Now that you know what you are doing, readjust and rearrange all the remaining field names and data entry boxes until your Form looks like that illustrated in Fig. 7.24
When you have completed the rearrangement of the Form, click in the Close box for the window, then click on Yes when prompted to save the changes to Form1
Now double click on Form1 in the Template Database dialog box to open it up as a data entry form
This is where all your hard work pays off. You should see the new form on your screen, and it
should look something like Fig. 7.24 above. You can now go ahead and start adding records to the new database.
7.6 ADDING RECORDS TO A NEW OR EXISTING DATABASE
Creating a new database based on the Template (Importing Objects)
The Access database Template is so named because you want to use this same template
document (with its table Table1) and data entry form (Form1) over and over for class records in the years ahead.
Before you start adding data about this year's students, you need to create a new database
based on this template document. You will use the new document to store actual student data. The template will remain unchanged for future use.
How you name documents is important. For the sake of this exercise, you're going to call the new database Roster 2002. The name indicates that the document will contain the database of student records for a year 2002 class. If you are currently teaching, you can use a different name if you want to use this tutorial to build an actual database for your own class. Substitute the name you choose for Roster 2002 as you go along.
The Template database should still be displayed on your screen. You can have only one (1) Access database open at a time, so Access will automatically close the Template database when you start to create a new one.
From the File menu select New... and, in the New File Task Pane, click on the Blank Database
In the File New Database dialog box that pops up, make sure your Access disk is the active disk and that you are inside the Database Documents folder
Type the new document name Roster 2002, and click on Create
Importing a Table
You are going to start by importing Table1 from the Template database. This will save you having to recreate the table all over again for the new class of students you're doing to put in the Roster 2002 database. This is where all that work on the Template comes in handy. You'll be able to use the Template over and over again for new classes in ensuing semesters or years.
Click on the Tables Object button on the left hand side of the dialog box
From the File menu, select Get External Data, then from the sub-menu select Import...
In the Import dialog box, double click on the Template.mdb database
A new dialog box (Import Objects) asks you to specify which table(s) you want to import from the Template.mdb database (Fig. 7.27).
Fig. 7.27 Importing an external database Table Object
You are asked to confirm that you want to import the Table1 object to the Roster 2002 database.
Table1 is the only table in the database, so click on Select All and click on OK
Importing a Form
Here are the steps to import Form1 from the Template database.
Click on the Forms button in the Roster 2002 database dialog box, then, from the File menu, select Get External Data > Import...
In the Import dialog box, double click on Template.mdb, then click on Form1 in the Import Objects window and click on OK
The Roster 2002 database now has a Table and a Form identical to the table and form that you created for the Template database. You can add student records to the database any time you like. So if you are not ready to insert new records at this stage, you can close the database. If you want to start entering data now, you can proceed with the tutorial.
Entering the data for the records in the database document
You should still have the Roster 2002 database open
Click on the Forms button in the Roster 2002 database dialog box, then double click on Form1 to open it
Remember that each record in the Roster 2002 database is made up of the data for a full set of 19 fields for each student. If you are an in-service teacher, and you want to add the data for all the students in your class, you should practice entering at least one or two records on your own. You might then want to see how your students could handle this job. There are a lot of data for each record after all. If you have a Windows computer in your home room, and you have a copy of Access to use with it, get your students involved in the data entry process. They will enjoy it, and you will be able to delegate an otherwise tedious task.
If you are a student teacher, you may or may not have the opportunity to complete this exercise with a real class. Whatever the case, you will need to enter a few records at this stage (four or five will do nicely for now) so that you can complete the rest of this tutorial.
Begin by selecting the First Name field of record #1 by clicking in the data entry box (the cursor is probably waiting there, unless you have already clicked elsewhere)
Type in the First Name of one of the students in your roster
The names don't have to be in any particular order since you can quickly sort them later on. Check the name you just typed. Is it correct?
If so, hit the Tab key to proceed to the next field (you'd press Shift-Tab if you wanted to move to a previous field)
Notice that the data are now stored in the data entry box for the First Name field in Record 1. If you wanted to correct the data, you would do so by clicking in the data entry box and then make any changes as if you were in the word processor.
Continue entering data for Record 1 (the data for the first student in the roster); make up the data as you go along if you don't have actual students in mind
Adding a New Record to the database
After you type the last field entry for each record, press Tab, and Access will
automatically present a new record Form
Access will also automatically save each completed record into the database on your disk, so you don’t have to worry about losing your work as you go along, and you also won’t need to save the database at the end.
Type the entries into each field one by one; be sure to press the Tab key after each entry to get to the next field (eventually you should have at least 4 or 5 records in the Roster 2002 database)
Adding a record to an existing database is just the same. New records are always added at the end of the database. If you want the new record to appear in a specific place in the Datasheet view you would use the Sort function. You'll learn how to do this in Lesson 8.
The new record will always have the same layout and format characteristics as the others in the database.
7.7 VIEWING THE DATA IN THE DATABASE
At this time, you should learn some of the ways to move about in a database. Access makes it easy for you to get around. Take a few moments to move from field to field and from record to record. Try these exercises.
Click anywhere in the data box next to a field name
This allows you to edit or add data if needed.
Hit the Tab key to move to the next field in the same record
To move to the same field in the next or preceding record, click on the left or right arrows in the Navigation tools at the bottom of the Form window (Fig. 7.28)
Fig. 7.28 The Access database navigation tools
To move quickly through the database as a whole, it's easiest to use the Datasheet (Table) view of the database, so…
In the View menu (Fig. 7.29) select Datasheet View now to see how you can view all the records in one window
Fig. 7.29 Datasheet View in the View menu
Scroll up and down and from left to right to get a feel for how to check out the records (rows) and fields (columns) of data that make up the database of student records.
Close the Datasheet View window on Table1
In Lesson 8 you will learn other ways of searching through the database, other ways of selecting sets of records from the database, and so forth. For now, as a final exercise in Lesson 7, let's just learn how to update the data in an Access database.
7.8 UPDATING ENTRIES AND RECORDS IN THE DATABASE
The following exercises will give you the opportunity to practice working with the data in a database.
Updating the contents of a field (one item of data in a record)
From the View menu, select Database Objects > Forms
Double click on Form1
To clear (delete), or otherwise change the contents of a field in a record, click on the field data entry box, then erase characters or words in the same way as you would if you were in the word processor.
Deleting (clearing) a record
In order to delete a single record from a database, you would select the record that you want to remove, then select Delete Record from the Edit menu, and voilà !
Try this for practice.
Click on the second record in the Roster 2002 database to select it
From the Edit menu select Delete Record
Access will warn you that you are about to delete a record and tell you that you will not be able to undo the deletion. This gives you the opportunity to change your mind, which you must do in this case, because Access will otherwise immediately permanently remove the record if you click on Yes.
Click on No in the dialog box
Deleting several records at once
You can use the Datasheet View to clear several records that are listed next to each other. To delete several records at one time here’s what you do.
You need to be in the Datasheet View of the database, so from the View menu select Datasheet View
Now drag in the left hand margin of the Table to highlight as many adjacent records as you want to delete
From the Edit menu, select Delete Record
The database will warn you that you are about to delete however many records you have selected, and will ask you if you are sure you want to do this
And you would go ahead and delete the records or not depending on what you want to do. Notice that the Delete Record command completely removes a record, such that it is irretrievable.
8 MORE ON DATABASE USE
Manipulating the Data in the database
8.1 GETTING STARTED
What are the some of the advantages that electronic databases have over manual record keeping systems such as file cabinets, folders, rolodexes, and the like? By the time you completed Lesson 7 you must have had a clearer idea of what a database is, and can probably now come up with an answer to this question.
Here, for the record, are a couple of the reasons frequently forwarded for using a database. Can you find your response among them?
• One can store huge amounts of data in a small amount of space (on a 3 1/2" disk one can store about 500 pages of text, or over 7000 records, where each record might have up to 100 characters; on a zip disk you can store the equivalent of over 70 floppy disks; on a hard disk you can store the equivalent of dozens of zip disks, and so on);
• One can easily manage all the data--view records, add new records, delete old records, or change existing records. You learned how to handle these tasks in Lesson 7.
Here are some other reasons for using an electronic database.
• One can quickly find anything one needs to know, and get answers to questions about the data in the database. What is Larry Dorey’s phone number?
• One can home in on a small subset of the data. Which students have a GPA below 2.0?
• One can instruct the computer to arrange (sort) records, and thus organize the data any way one likes. I'd like a listing with everything sorted based on the student hobby field. That'll make it easy for me to identify students who have the same interests.
• One can use the computer to create any number of reports for the purpose of supplying
others with information pertaining to the data in the database. My principal, Donna
Hendry, wants a monthly report of student progress in certain subject areas.
The purpose of this tutorial is to help you learn how to use the Access database in these last four ways. By the end of the tutorial you will have the basic skills you need to become a power user of the database. Whether you ever can call yourself a skilled user will depend, of course, on whether or not you continue to use Access as a tool to support your professional work in the classroom.
You should be very familiar with how to open the Office Access program by
now, so go ahead and do so
Showing the Full menus and organizing the Toolbars
In case you're using a computer in a lab or some computer other than your own, you should set the options to Always show full menus, Show the Standard and formatting toolbars on two rows, List font names in their own font, and Show ScreenTips on toolbars.
You may recall doing this at the beginning of all the previous lessons. If the computer you're using doesn't already have these settings, here's what you do.
In the Tools menu, select Customize…, and in the dialog box that pops up, select the Options tab (Fig. 8.1)
Fig. 8.1 The Customize dialog box
Make sure there is a check mark next to the item to Always show full menus
While you're at it, check the box next to Show Standard and Formatting toolbars on two rows, List font names in their font, and Show ScreenTips on toolbars
Click the Close button when you're ready
On with the tutorial…
When you are ready you should have the Access Task Pane on the right of the window, inviting you to either create a new database or open an existing database.
Put your Access Disk in the disk drive
You are going to work first with a database similar to the one you created in Lesson 7--the name of the document is Students 2002. It is possible you never completed Lesson 7, so a document called Students 2002 has been prepared for you.
Open the Students 2002 database
Click on the Forms button among the Objects buttons on the left side of the dialog box and double click to open Form1
You should see the Forms View illustrated in Fig. 8.2.
8.2 REVIEWING THE BASICS
You may not have worked with databases a great deal prior to using these tutorials, so it will be a good idea to start by recapitulating what you learned in Lesson 7 by way of reinforcement. Then you can move on to learn about the new features of the Access database that are targeted for this tutorial. Let's start with terminology.
A database is a document which contains a set of records. Each record is made up of one or more fields, and each field may or may not contain entries.
For example, a database of movie titles might have records for a few thousand movies. Each record will have fields for the movie title, the director, the producer, the star(s), and so on. A particular entry for the Movie Title field might be "Star Wars."
Viewing the data in the database
When you first open an existing database into primary memory, it is generally best to open a Forms View of the database because you can easily view the contents of an entire record on the screen, thus showing you what all the records basically contain.
In the Forms View, as you can see, the data for one complete record is shown in the Form. Notice the database navigation tools at the bottom of the Form window (Fig. 8.3).
Fig. 8.3 Navigation tools in the Access database
Look them over now to re-familiarize yourself with how they work. The Datasheet view (Fig. 8.4) shows the records across the screen in rows and columns much like the Spreadsheets you learned to use in Lessons 4 and 5.
Fig. 8.4 Viewing records in the Datasheet view
Adding a record to the database
You can add a new record any time you want, wherever you may be in the database, because you can tell Access to sort everything into a specific order whenever you need to do so. You will learn how to do this in a later section of this tutorial (8.4 SORTING RECORDS). You are going to add a couple of new records now.
Make sure you have Forms View selected (from the View menu) when you want to add a new record
In the Insert menu, select New Record (or click on the New Record button in the Navigation tools at the bottom of the Form window (Fig. 8.2) which will open up a set of empty fields for the new record
Enter the data for this new record, and at least one other, record (two records in all), pressing the Tab key after you type the data for each field
You can put junk entries in some or all of the records you are adding because you will practice deleting them in a moment. For that matter, you can skip a field if you like by simply pressing Tab without entering any data.
Clearing a field in a record
First you need to know that you cannot clear entries from a field that has been defined as
requiring data of some kind in the field. If you try to clear data from these fields (in our database the First Name through the Home Contact fields, for example) you will get a warning prompt from Access telling you that you cannot have a null value in these fields.
So click in the Nick Name field entry (not on the field name) of the first of the records you added just now
Highlight the field entry, then from the Edit menu select Cut (Ctrl-X)
Next go to the second of the records you added a moment ago and click in the Clubs field entry
Once again select the entire field entry, and from the Edit menu select Cut (Ctrl-X)
Notice that this does not delete the field or the record; it simply clears the selected entries. The field is still there, so new data can be entered into it.
Deleting (clearing) a record, or a set of records
Use the Navigation tools to locate one of the records you added a moment ago
From the Edit menu select Delete Record (or click on the Delete Record icon in the Access toolbar—Fig. 8.5)
Access immediately displays a warning dialog box to tell you that if you go ahead you will permanently lose the data in the record.
Click on Yes to delete the selected record
Notice that this removes the record(s) entirely.
Changing the data in a field
Suppose some data gets entered incorrectly--through misspelling or a typo. Let's say you needed to update a nickname in a student's record. To change it you would do the following.
Locate a record in which you want to make the change (for this exercise select any record you like)
Click on the entry next to the field name that needs to be changed (use any entry in the Nick Name field for this exercise)
Change it to some other nick name in the same way you would in a word processor
All of the exercises in this section were by way of recapitulating what you learned in Lesson 7. Now let’s move on to learn some more advanced features of the Access database.
8.3 SEARCHING AND QUERYING A DATABASE
This is a powerful function of the Access database. You can give Access any set of characters as the key for a search of the database, and the system will find that set of characters, if it exists, even if it is a subset (a part) of a larger word or phrase.
There are several ways you can do this. But first you need to open a new, larger, document onto the Access Desktop.
Close the Students 2002 database document and from the File menu select Open...
Your Work Disk should still be in the disk drive.
Open the document called Birds (on your Access Disk), click to select the Forms object on the left of the dialog box, then open the BirdSightings form
Finding a single record using data from a specific field
You often may need to locate and display the contents of one particular record, or set of records, based on data from one specific field. The function Find, which is in the Edit menu, is the Access command to do this. It works much the same as when you use the Find command in the word processor, which you learned about in Lesson 2.
Suppose, for example, you wanted to find the record for the bird with the Common Name
Northern Oriole in the Birds database. Here is how you do this.
First you have to select (by positioning the cursor anywhere in it) the field which contains
the criteria for the search, in this case the Common Name field.
Click anywhere in the Common Name field, then from the Edit menu select Find… (or click on the Find button in the toolbar—Fig. 8.5 above)
Access now displays the Find and Replace dialog box (Fig. 8.6)
Fig. 8.6 The Find and Replace dialog box
You must indicate exactly what data you want found by typing the word or string of characters that will be the search criteria. The record(s) you find with a find request are called the "found set".
In the Find What: entry box type Northern Oriole (if you do not check the “Match Case” box, Access doesn't care whether you use upper or lower case letters)
Click on Find Next then click on Cancel
Access quickly locates the record containing the name "Northern Oriole." This one record alone is shown on the Forms View screen. Of course, if you incorrectly typed Northern Oriole, Access would tell you that the search item was not found (Fig. 8.7).
Fig. 8.7 Warning message after unsuccessful search of the database
To see all the records surrounding the Northern Oriole, in the View menu select Datasheet View
Finding two or more records based on data from a single field
Next let’s search on criteria which will find more than one record. You want to find any bird with the primary color (Color1) that has any shade of black in its plumage.
Close the BirdSightings datasheet
Click on the Forms tab and double click on the BirdSightings form to open it, then click in the Color1 field
Click on the Find button in the Access toolbar (looks like a pair of binoculars), and type the word Black in the Find What: box; press Enter to tell Access to Find Next, then click on Cancel
The search finds and displays the first of several records where a bird has the color black in its plumage (Common Yellowthroat). To see all the records that result from a search, you must use a filter.
Filter by selection
The exercise that follows will use a filter to select the set of birds whose habitat is "marshes".
Close the BirdSightings Form, then click on the Tables button and open the BirdSightings Table
Scroll, if necessary, from left to right to locate the Habitat column
Now position the mouse arrow near the left edge of one of the marshes entries (the mouse pointer will become a hollowed out plus sign ( )—Fig. 8.8) and click the mouse button
Fig. 8.8 The cursor changes to a hollowed out cross
This tells Access that you want the set of birds who live among marshes.
Click on the Filter by Selection button in the toolbar (Fig. 8.5) or from the Records menu select Filter/Filter by Selection
You will now see a list of 15 birds in the database that live in marshes (Fig. 8.9).
Fig. 8.9 The found set of filtered records
Click the Remove Filter button on the toolbar (Fig. 8.5 back a couple of pages), or from the Records menu select Remove Filter/Sort to go back to viewing all the records in the database
Filter by Form
From the Records menu select Filter/Filter by Form or click on the Filter by Form button in the toolbar
Access presents an empty form for you to select the criteria for the filter (Fig. 8.10).
Fig. 8.10 The Filter window with its Look For and Or tabs
But first you need to tell Access that the previous filter (for the birds that live among marshes) is no longer relevant. Access keeps previous search criteria active unless you switch them off or remove them from the filter. So you need to remove "marshes" from the Habitat entry box. This is easy enough to do.
Highlight “marshes” if it isn’t already highlighted and hit the Backspace key Click in the Color1 field
You will see an arrow at the right hand edge of the Color1 field (Fig. 8.10 above) indicating a drop down list.
Click on the arrow on the right edge of the Color1 data entry box to see the list of the possible colors in the Color1 field (Fig. 8.11)
Fig. 8.11 The drop down list of colors
Click on the color “black” in the list, then click on the first Or in the Look for filter rule icons at the bottom of the Filter by Form dialog box (Fig. 8.9)
This will bring up a new empty filter dialog box.
Click on the arrow again to see the list of the possible colors in the Color1 field and in the drop down list click on “blackish green”
Click on the next Or in the Look for filter icons at the bottom left of the dialog box, click on the arrow again and in the drop down list click on “bluish black”
That is all the colors in the Color1 field that have black in them.
Now click on the Apply Filter button in the Access toolbar (or select Apply Filter/Sort from the Filter menu)
Immediately Access filters out the set of birds with black in their plumage (Fig. 8.12).
Fig. 8.12 The result of the color filter
Searching based on criteria from more than one field
This time you are going to give Access two or more search criteria for your search of the
database. The system will search every field of every record, locating those records that contain the set of characters that constitute the search criteria. Access will then display the complete contents of all the records that contain the search criteria.
Click on the Remove Filter button in the toolbar (or select Remove Filter/Sort from the Records menu)
It doesn't matter where you are in the database to begin the search. Access will always search the entire database for the records you request. For instance, let's say you want to find all the records for birds that you spotted in the month of September. You know there were several, but you don't remember which they were. Here are the steps you would follow to do this.
First you need to remove any previous filters from a previous search because Access
remembers them from search to search.
Select Filter by Form again and remove the “black” filter in the Color1 box by hitting the Backspace key
Now hit the Or tab in the filter search expression (Look for) tools at the bottom of the window
Hit the Backspace key to remove the criterion “blackish green”, click on the next Or, and hit the Backspace key to remove “bluish black”
Now you are ready to create the new filter.
First click on the Look for tab, then scroll across if necessary to the Date Sighted field entry box and click in it
Click on the arrow that pops up to see the drop down list of all dates on which birds were sighted
Click on the first date which has a ‘9’ for the month (September)
After you click on it, you’ll see the first date in the entry box with ‘#’ symbols around it to indicate its selection by the filter.
Click on the first Or tab, then click on the arrow in the Date Sighted box again and select the second date with a ‘9’ for the month
Now click on the Apply Filter button in the toolbar
How many records did you get? There should be 12.
8.4 SORTING RECORDS
The three major advantages of an electronic data storage system such as a computer disk over a manual, paper-based filing system are
1. the organizational flexibility (making change easy);
2. the enormous reduction in the amount of room required to store all the data (saving
space);
3. the greatly increased speed of data access (saving time).
When we use filing cabinets to store data we usually come up with some kind of organization. So we put labels on the drawers in the cabinets and labels on the folders inside the drawers. This organization is often based on numeric or alphabetic sequencing. The purpose, of course, is to enable us to quickly find what we have stored in the appropriate folder in the appropriate drawer.
This is a good system as long as we have no cause to frequently change the organization we have set up, and assuming space and time are not at a premium in our day to day operations. Unfortunately, change, space, and time are factors critical to our productivity, so we should welcome any system which helps us cope with change and optimizes our use of space and time.
Today, using state of the art technology, we can electronically store the equivalent of the
Library of Congress in a shoe box. And we can now access any specific item in that "box" in a few thousandths of a second. What is more, software such as Access makes it possible for us to organize that data more or less on the fly.
Sorting on a single field
Take, for example, the Birds database that is on your Desktop at this time. As you can see, the bird names (Common Name field) are not sorted in any particular order. In fact, the database is sorted based on the Date on which each bird was Sighted.
So the ordering is chronological rather than alphabetical. It would therefore be difficult,
especially if the database were very large, to visually find the record for a particular bird (which is why Access makes available the Go To Record, Sort Records and Match Records functions under the Organize menu).
What if you wanted to print out a report listing all the birds in alphabetical order by name of bird? You would have to first organize the records alphabetically on the bird names. Fortunately Access has a Sort Records... function which enables you to complete this operation with the greatest of ease.
First you need to remove the filter you used to select the set of birds sighted in September.
Click on the Remove Filter button in the toolbar
You can best see the results of a sort if you are in the Datasheet view of the database, which is the view you should have on your screen. If you do not have the Datasheet View, select it now from the View menu before proceeding.
Click anywhere in the Common Name field
Click on the Sort Ascending button in the toolbar (Fig. 8.13)
Fig. 8.13 The Access Sorting tools
Immediately Access sorts the records alphabetically on the Common Name field. There are other sorting options depending on the type of data in the field you are using as the basis, or key, for the sort:
• If your sort request is based on a Text field such as Common Name or Location Sighted, the sort will be either Ascending (A to Z) or Descending (Z to A).
• If you are sorting on a Number field such as the Count field, you would be able to sort in Ascending (Low number to high number) or Descending (High number to low number) order.
• Finally, if you are sorting on a Date field such as Date Sighted, you would be able to sort in Ascending (Chronological) or Descending (Reverse Chronological) order.
8.5 CREATING REPORTS
A word about the Access database reporting facility
• Planning is still an important prerequisite for good report definition. No matter how
tempting it might be, always resist creating a report without first thinking and mapping out what you want as the end result.
• Changes you make to data fields for the report do not affect the data in the database at all. So you don't have to worry about losing data, or messing up the database when you create reports.
• You can create reports that contain just a few fields from a selected set of records by using the search criteria techniques that you already learned about in this lesson. For instance, you could use the Birds database to print a report listing just the name, color, and count of birds that were seen in your neighborhood. Shortly you will practice doing something along these lines.
• You can create numerous layouts or reports for a database, each report having different sets of data, and you can store them all along with the database.
In the next sections you will work with the Birds database. You are going to create a simple report.
Initial definition of the layout or report
You are going to create a layout or report format which will list all members of the duck family sighted during the reporting period covered by the database. The fields in the report will include the Common Name, Family, Date Sighted and Count fields.
A word about selecting tabular layouts
Bear in mind that most databases have many fields. If there are more field names in a report layout than can fit across the screen or page, the names simply wrap around, creating two or more lines, if necessary, in the top section (the Header) of the layout.
This can make for very untidy, not to say confusing, reports. The columnar report you are
about to work with will not have this problem because you will be using only a small selection of the fields in the database (just 4 to be exact), but it is good to be aware of the problem should the occasion arise where you want to create a report that uses many fields.
On with the show!
You need to start by using a filter to create a new Table. You want to select only the birds in the Duck family for the Report.
Click on the Filter by Form button in the toolbar, then click in the Family field
Now click on the arrow to see the drop down list of all possible families of birds
Click on Duck, then click on the Apply Filter button in the toolbar
Next click the little arrow next to the Objects button at the end of the Access toolbar (Fig. 8.14) to show the drop down list of database objects
Click on the Report icon
Fig. 8.14 The Report object in the database objects drop down list
You have to save the Table before you can work on it, so click on Yes in the dialog box that pops up on the screen
This brings up the New Report dialog box.
In the New Report dialog box select Report Wizard (Fig. 8.15)
Fig. 8.15 The New Report dialog box
Select the BirdSightings table as the source for the report’s data, and click on OK
In the Report Wizard dialog box (Fig. 8.16) from the Available Fields:
double click to select the Common Name, Family, Date Sighted, and Count fields, then click on the Next button
Fig. 8.16 The Report Wizard dialog box
Fig. 8.17 The Report Wizard Style selector dialog box
Access now asks you in a new dialog box if you want to add any grouping levels to the report.
You don’t want to add any grouping levels, so click on the Next button again to by-pass this dialog box
In the next dialog box you are asked if you want to sort the records. There is no need to do this since you already sorted them in Ascending order on the Common Name
Click on Next once more to proceed to the next step in the Report Wizard
The default setting for the report layout is Tabular, which is what you want. The default setting for the orientation is Portrait, so leave that as is, too.
Click on the Next button, then select Compact for the style of the report in the next dialog box (Fig. 8.16 on previous page) and click on Next
In the next step of the Report Wizard you have to enter the title for the report (Fig. 8.18).
For the Title of the report, type Count of Ducks Sighted, then click on the radio button next to “Modify the report’s design”
You need to select this option to modify the report’s design since, as you will see shortly, you need to rearrange the headers and fields so they fit nicely on the page of the report.
Click on Finish
8.6 IMPROVING THE REPORT (MAKING CHANGES)
As you can see from Fig. 8.19 on the next page, the new layout which you named Count of Ducks Sighted needs some work.
Fig. 8.19 The unmodified report in the Design View
After completing the following exercises, the report will look like Fig. 8.20.
Fig. 8.20 Final version of the Count of Ducks Sighted report
The Modify window for the report has five sections to it: a Report Header section, a Page Header section, a Detail section (where the data will be listed), a Page Footer and a Report Footer.
In the Page Header section, the Common Name column looks about right, so there’s no need to change it. The space allowed for the Family column, however, is much wider than necessary.
Click once on the Family Page Header so you get the handles around it
Now grab one of the handles on the right edge and drag the box to the left till it is only a little wider than the header Family itself
Do the same with the Family box in the Detail section of the report
Click now on the Date Sighted Page Header (part of the header is truncated), grab one of the handles on the left, and stretch the box so it is wide enough to show the whole Date Sighted Page Header
Next position the mouse arrow along the bottom edge of the Date Sighted Page Header box so the arrow changes to a small black hand, and drag the box to the left so it ends up close to the Family Header box Now do the same to the Date Sighted Detail box
Next, grab the Count Page Header box and the Count Detail box and slide them over so they are next to the Date Sighted boxes
Your last task is to center the report title.
Click on the title Count of Ducks Sighted
Position the mouse arrow along the lower edge of the box till the arrow changes to a small black hand, then drag the title to the right till it is centered over the columns of the report
When you are done, the Design View for the report should look like Fig. 8.21.
Fig. 8.21 The final Design View of the report after modifications
From the View menu select Print Preview to check out the appearance of your report.
It should look similar to that illustrated in Fig. 8.20. If you need to go back and do some more modifications to the report, just go back to the View menu and select Design View.
A word about where and when Access saves your Tables, Forms, and Reports
All the Tables, Forms, and Reports that you design to go along with your Access databases are automatically saved with the database itself. In other words, they’re not saved as separate files or documents. So if you want to open a Table, Form, or Report, you must first open the database they belong to. Then you’ll be able to access them (hence the name of the application) check them out, re-design them, and so forth.
8.7 PRINTING REPORTS
Once you have prepared the new report, it is a straightforward process to print the report. The command to do this is the same as you have used to print any other documents from within Microsoft Office, whether you have been working in Word Processing or the Spreadsheet.
Click on the Print button at the top of the Print Preview window to print the Count of Ducks Sighted report
Remove the report from the printer and take a closer look at its contents. Notice that the only records in the report are those relating to ducks in the Family field. These are just a small subset of records drawn from the larger Birds database.
