For Monday, April 2nd's class:
Welcome to the first day of baseball. Before starting the section on Microsoft Access, I would just like to follow up on the home work assignment that most of you have finished for me. On an upcoming exam, I may ask you to report your statistical findings in layman terms. That is, aside from producing a univariate analysis of variance, you will go further, explaining to me if there were any significant interactions or main effects and what you found in the post-hoc analysis. So for the last assignment, you would have stated something like this:
There was not a significant interaction between sport and layout (F=.184, p=.833). However, there were two significant main effects. One for Layout (F=9.23, p=.004) and one for Sport (F=3.49, p=.038). For Layout, observation of the means showed that Steve's layout generated more hits than Jim's (271.07 vs. 257.43). For Sport, Tukey's Post Hoc Analysis showed that the sport of football generated significantly more hits (269.85) than baseball (256.05).
Now for today, please read the lectures for Unit A and B. Then follow along with the instructions in your book for Unit A and B. Your assignment, which will be due Monday, April 9th before 2:00 pm, will be listed at the bottom of Unit B. Play ball!
Welcome to Microsoft Access. The following lectures are to be used along with your text book. It is highly advisable that you first read the lecture notes pertaining to each specific unit before attempting to work through the units in the text. Assignments can be found at the end of lecture Unit B. You are to submit these assignments, for now, to the following address:
Unit A
Lesson #1
Microsoft Access 2000 is a database software program that comes as part of the Microsoft Office 2000 suite of programs. A database program manages data. Access 2000 is a relational database program in that is manages lists of related information. These lists can include products, customers and vendors, classes, students, and enrollments.
Prior to the advent of computerized database systems, data was entered and stored on manual systems. Writing data on a system of index cards was tedious, often error prone, and very inflexible.
Database software has many advantages over a manual system of storing data. These include: Faster and easier data entry and information retrieval, many options for viewing and sorting the data, increased data security, facilitated sharing data of data among several users, and minimized duplicate data entry.
Rather than store data on index cards where each card often had to repeat data, by setting up your tables correctly in Access you relate the tables and link them through common fields to guarantee the integrity of the data.
Figure A-1 shows how tables related through common fields. The Cust No field is duplicated in the Customer Table and the Sales Table to allow Access to find the appropriate address for the sale. Customer and Products information are only entered once.
Figure A-1: Using Access, an electronic relational database to organize sales data.
You should know that there are important distinctions between Excel and Access. When you view a table in Datasheet view it looks like and Excel spreadsheet. You may know that Excel can handle lists and simple lists, but Access has many robust features that go far beyond the capabilities of Excel to manage data. Access has great storage abilities, allows links between tables, provides flexible and expanded reporting, provides security features to protect data in many ways, allows for multiple user access, and has customizable forms to facilitate data entry.
Lesson #2
Understanding basic database terminology will help you to learn how to use Access. A database is a collection of information. The smallest piece of information is a field. The key field contains unique information about each record. A group of related fields is a record. All the records for a single subject is collected in a table. A table is the most important object. There are seven objects in Access. These include tables, queries, forms, reports, pages, macros, and modules.
Access is a relational database. This means that more than one table can share information.
Tables contain fields and records. Figure A-2 shows how tables are organized.
Figure A-3 shows the relationship between the Access Objects.
All seven objects are stored in the database and have specific purposes:
Table: stores all the data and presents the data in a spreadsheet-like grid called a datasheet.
Query: provides a datasheet view of the data in the table but 'answers a question' about the data and can pull selected fields or records from one or more tables in a database based on criteria or a 'question'
Form: provides a user-defined way to enter data into the table. Generally you see one record per screen and the layout is customizable to best match t he needs of the user.
Report: provides a professional printout of the data.
Page: creates a Web page with connectivity to the Access database.
Macro: stores keystrokes and/or commands that can be played to facilitate any action in Access.
Module: stores Visual Basic programming code.
Lesson #3
Access is installed as part of the Microsoft Office suite of programs. To start Access, you have to click the Start button on the task bar, point to Programs. Access should be on the Programs menu. To start the program, click Microsoft Access. A dialog box opens giving you several options. You can open an existing database by selecting from a list of the most recent ones worked on, you can click More Files to explore your hard disk and find another database, or you can open a new blank database. Click the More Files and then click OK to open the Open dialog box. Locate the database you want to open, and then click Open. Once you open a database, you will see the database window open inside the Access window as shown in Figure A-4.
The Access window has a series of menu options and a toolbar for specific functions. The database window has the Objects bar on the left side as well as a menu bar at the top. These buttons are used to work with the seven objects in the database.
You cannot use the Save As command to make a copy of your original database. It is advised that you use Windows Explorer to copy and rename the database if you want to retain the original for any reason.
Lesson #4
The database window includes many of the common elements you have seen in many Windows programs. When you start, there are two open windows, but you only need to look to the title bar, to determine which window is the Access window and which window is for the open database.
The Window includes a title bar, a menu bar and toolbars. There is a status bar to provide important messages as you work.
The File menu contains commands for working with the open database such as opening, saving, and printing.
You click each of the buttons on the Objects bar to display each of the seven object types. When you click the Queries button on the Objects bar, for example, you will see all the queries that are currently in the open database. The database in Figure A-5 has three queries.
Once selected, by using the buttons at the top of the database window, you can perform all the required actions. You can Open the selected object, Design a selected object, create a New object (of the selected type.) The database window also provides access to a Wizard for each object to help you create the object. The Design button is used to create the objects. The query and table objects have both a Design view (available through the Design button) and a Datasheet view (available through the Open button.) As you learn more about creating and using objects, you will become familiar with each of these buttons.
View buttons work similarly to the View buttons in Windows Explorer letting you view the objects as Large Icons, Small Icons, in a List, or with Details.
If you click the Objects button on the Objects bar it will collapse (or expand) the list. If you click the Groups button on the Objects toolbar it will collapse (or expand) the list of Groups. Groups provide a way to organize your objects. You will learn more about Groups later.
Lesson #5
The Table object contains all the data in a database. All objects have several views. The two you learn about first are Design view and Datasheet view. It is important that you learn how to navigate or move between the records of the table Datasheet view. The datasheet looks similar to a spreadsheet. To view the datasheet, click Tables on the Objects bar, then click the Open button. Figure A-6 shows the datasheet for a Customer's table.
You can maximize the datasheet window to see as many records and fields as possible in the window. The field names are at the top of the columns each row is a record. Vertical scroll bars appear if there are too many rows (records) to fit on the screen; horizontal scroll bars appear if there are too many fields (columns) to fit across the screen.
Focus refers to which field would be edited if you started typing.
The Record selector button selects an entire record. A selected record will have a black arrow symbol to the left of the first field. Navigation buttons are located at the bottom of the window. These help you move from record to record and tell you the total number of records in the table.
You can press the Tab or Enter keys to move from field to field through a record, you can click a specific record or field on the datasheet, or you can use the arrow keys.
There are several shortcut keys also available that you can master as you learn to navigate the datasheet.
For example:
[Tab], Enter or right arrow moves the focus to the next field of the current record
[Shift][Tab] moves the focus to the previous field
[F5] opens a window where you can specify a record to go to.
[Ctrl][End] Moves the focus to the last record
[Ctrl][Home] Moves the focus to the first record
Lesson #6
In order to keep your database current you have to know how to enter new records to the table. You will learn that entering data directly into the table's datasheet isn't always the best way. However, this is a critical skill. First, you have to open the Datasheet view by either double-clicking the table name on the database window or clicking the table name and then clicking the Open button.
There are several ways to enter a new record. You can click the New Record button on the Table Datasheet toolbar or the New Record Navigation button. You can also press Enter at the end of the last record to generate a new record in the table datasheet. To Enter a new record, click the New Record button. If the table includes an AutoNumber data type field Access will enter the next sequential number.
Figure A-7 shows a datasheet that is ready to accept a new record.
When you type data into fields there are a few things you should know. Some fields will accept only certain types of data. You will learn about this later. There are also AutoNumber fields. Access generates sequential numbers to populate these fields, you do not enter data in them.
Your datasheet navigation skills can help you enter new records. However, the simplest way to move from field to field is to press Tab after each entry.
When you enter a new record, be sure to complete all the fields (if possible.) You should also know that you do not have to click the Save button, once you enter the data, the record is automatically saved.
You can also use the datasheet to change the way the fields are listed. If you need to enter data in a certain order, you can drag a field to the right or left to change its location in the datasheet.
Lesson #7
Updating the existing data in your database is the way you keep you data current. Editing can mean changing a name, price, or address in a record or deleting an entire record. Editing records is a critical task in maintaining databases.
You will learn the different ways to access the data in a table. For now, you should learn how to edit records using the table Datasheet view.
In this view, you use your navigation skills to move from record to record. Editing data in the fields means changing text in the fields of the records. You can select the data you want to change, then type the replacement text. You can also use the Delete and Backspace keys once you are in the desired 'cell' in the datasheet.
The Edit Record symbol let's you know you are editing a record. The first record is being edited in Figure A-8.
Shortcut keys, such as [Ctrl][ ' ]which inserts the value from the same field in the previous record into the current field, which can help you in your editing efforts. Another valuable shortcut key is [Ctrl] [;] which inserts the current date.
Undo is available to undo your last action. If you place your mouse pointer over the Undo button it will tell you the action that can be undone.
Esc removes the current field's changes; pressing Esc twice removes all the changes to the current record you are currently editing.
You may also find that you need to resize the width of the columns as you work with the Table's datasheet. This is a simple procedure, not unlike resizing columns in Excel or many other grid/based views in Windows. Place the mouse pointer on the right edge of the column head. The resize pointer will display and you can drag the edge of the column to any size. You an also double-click the edge of that column divider to resize the column to display the widest entry.
You should take advantage of Spell Check that is available on the Tools menu or by pressing F7 to be sure you don't have errors in your data.
Lesson #8
There are times when you are going to want to look at the data in a table printed on hard copy. Large databases can generate a lot of paper. You may want to restrict printing to certain records or a specific number of pages. Prior to printing a datasheet, you should Preview the datasheet to be able to determine how it will look when printed. Click the Print Preview button on the Database toolbar to view how the printout will look of the selected object. If the Table is open, click the Print Preview button on the Table Datasheet toolbar to open up the Print Preview window. The datasheet displays in miniature version as shown in Figure A-9.
The Print Preview toolbar provides several tools to help you set up your printout. You can chose to view the datasheet one page to a screen, or by selecting two, three, or any number of pages per screen. There are page navigation buttons at the bottom of the window to help you view all the pages.
Other buttons on the Print Preview toolbar provide commands to change the Zoom if you want a closer look at the datasheet, you can click a button to return to the database window, and you can even create a new object directly from the Print Preview Window.
Once you are sure you want to print the datasheet, you can click the Print button on the Print Preview toolbar. If you don't want to go through a Print Preview window, Print is available on the Table Datasheet toolbar.
Click File on the menu bar and then click Page Setup to open the Page Setup dialog box as shown in Figure A-10.
If you want to change the page orientation (you can select Landscape or Portrait) click the Page tab. To change the page margins, you click the Margins tab. You can take advantage of margins to help fit a datasheet to a page.
Lesson #9
Access is a very powerful and robust program with many features. Fortunately, the Access Help system is always available to provide you with many types of help. You may find the system familiar if you have worked on other Microsoft program. If you need help, you should click the Help button on any toolbar at any time. The Help system has many options. To use the Office Assistant you type a question into the dialog bubble and then select from a list of choices to answer your questions. There are several good tutorials available that work you through learning concepts such as what tables are and how they work as shown in Figure A-11.
You can also search for information by looking through an index, by Answer Wizard, or by a table of contents. If the Help files in Access don't answer your question, you can go out to the Web to find more information directly from the Access Help menu. The Help system also includes a comprehensive glossary.
Detect and repair, also available on the Help menu, will find any problems with the application and repair them.
When you have completed your work on the Access database you need to close the database and then exit Access. To close the database and exit the program, click File on the menu bar and then click Exit. If you just want to close the database, you can click File on the menu bar and then click Close. You can also use the Close and Close Window buttons on the Title bar.
If you are using a floppy disk to store your database, you should not take the disk out of the drive until you have exited the program.
Unit B
Lesson #1
The table object contains all the raw data in the database. Relational databases have more than one table. When you design a database you have to carefully plan the fields that will be in each table. You need to know the purpose of the database and give it a name that will identify the data in the database. Think about the type of information you want the database to provide in printed reports. If the data currently exists in another form, you have to collect the raw data for entry.
You should sketch out the fields and data types for the tables before you start creating the database. The data type for each field determines the type of information that you can enter in each field.
The available data types are as follows: Text, Memo, Number, Date/Time, Currency, AutoNumber, Yes/No, OLE Object, Hyperlink, and Lookup Wizard.
When assigning data types, you should assign the Text data type, not choose Number for a telephone or zip code field. While these fields generally only contain numbers, they should still be text data types. When you sort the fields, you'll want them to sort alphabetically, like text fields. If a zip code field was designated as a Number data type, the zip codes would be interpreted incorrectly as the values
You determine the data type for each field in Design view. Figure B-1 shows a table in Design view. You determine the field name and data types for each field; data types determine field properties.
Lesson #2
There are many ways to create a table in Access. You can import it from another data source such as a spreadsheet, you can create it from scratch, or you can use the Table Wizard to help select fields based on the type of database you are creating and set up the appropriate data types.
When you are ready to begin to create a new database, start Access and then click the blank database option button. Name and save the database with a name that will help identify the purpose of the database. If you use the Table Wizard to create a table, you can select fields from two categories; personal or business.
Figure B-2 shows the Table Wizard with fields selected.
As you work through the Wizard, you will select the fields to include in the table from a list. You can rename the fields in your new table by clicking the Rename Field button in the Table Wizard. Click the Select Single Field button to move a field at a time to the new table, click the Select All Fields button to move all the fields from the Sample Fields list to your new table. Each field has predetermined characteristics that can be changed by you later.
You can pick which of the fields will be the primary key field or have Access make that determination. The primary key field is the field that will contain the unique information about each record in that table.
Once the Wizard creates the table, you can open the table in Design view. Design view is where you make changes to the structure of the table object. The primary key field is identified by a key symbol next to the field name.
Lesson #3
You modify the table structure in Design view. You can add, delete, or change field names and data types. Field names and data types are required. Field descriptions, which are optional, further document what type of data the field will contain. To enter a field, you type the field name in the Field name cell, press the Tab key, enter a data type (you can select from the list or press the first letter of the data type), and then enter an optional field description.
Field properties are additional characteristics that define each field. The Caption property can be used to override the technical field name with a simplified or descriptive caption entry when the field name is displayed on datasheets, forms, and reports. When you create a table using the wizard, many fields have caption properties.
The Field Properties section of the Design view window lists all the field properties for each field. You can click any of the displayed field properties at that time and make any changes you have to. For example, if you want a state field to be limited to the two letter code, change the Text field size to 2 rather than the default value of 50 so that New York, for example, will always have to be NY. Field properties differ slightly for each data type and include the field size, default value, caption properties, and field validation rules and properties. The selected field will display its properties in the lower section. You can modify field properties in Design view.
Figure B-3 shows the Field Properties section for a field with a Currency data type.
Lesson #4
Datasheet view is where you can enter and view the table data. Even though you will most likely use Reports to provide printed output for your database, you may want to print the datasheet. You can format the datasheet to better present the data that is in the table. When you view the table in Datasheet view, you can click Format on the menu bar to see the many options available.
To change the font of the text in the datasheet, click Format on the menu bar and then click Font. The Font dialog box has option for changing the font style, the font size and the font color.
Click Format on the menu bar and then click Datasheet to open the Datasheet formatting dialog box. Figure B- 4 shows the Datasheet Formatting dialog box.
Use this dialog box to change the cell effects, and gridlines. Gridlines are the borders of the cells. You can change the line styles for the vertical and horizontal gridlines independently as well as change the color of the gridlines. You can also change the cell effects. You have to exercise some design constraints when working with formatting. Sometimes no border works better on a datasheet, you can choose to not have horizontal, vertical, or both borders. A transparent border will make it appear as though there are no lines between the rows or columns.
Click File on the menu bar, and then click Page Setup to open the Page Setup dialog box. Use this dialog box to change the margins of the datasheet and determine whether it prints in Landscape or Portrait orientation. This dialog box helps you fit a datasheet to a single page for easier viewing.
Click the Print Preview button to get a preview of what the datasheet will look like when printed. If you don't like what you see in the print preview window, you can go back and make any changes.
Lesson #5
By default, the records in the datasheet are sorted by the value in the primary key field. You may want to view and print the datasheet based on a specific order. For example, you may have a list of products that includes a price field. You may want to see the products ordered from the most expensive to least expensive product. To view the data in this way, you would sort the list. Sorting reorganized the data based on the field and order you specify. You can sort in ascending or descending order. For ascending order, text fields sort from A-Z, number fields from lowest to highest, and data/time fields from oldest date to the most recent (or furthest in the future) date.
When you filter data, you temporarily "pull out" a subset of the data based on specified criteria. If you have a database of members who live in several states and you want to see all those members who live in New Jersey, you can Filter for all records that have NJ in the state field. The resulting datasheet will show you all the fields for all the records for only those records with NJ in the State field. Figure B-5 shows a datasheet that has been filtered for the music category "Rock". The number of filtered records, 16 for this datasheet, displays in the navigation bar.
Sorting and filtering do not make any permanent changes to the data in the database.
If you want to locate a specific piece of information in the database you can use the Find command. The Find and Replace dialog box gives you many options for not only finding a specific piece of data such as "Cats" or "Yellow" but gives you options for matching, looking only in certain fields, and then replacing the data with whatever you type in the box.
Wildcards are symbols you can use as substitutes for characters to find information that matches your find criteria. Access uses three wildcards: the asterisk (*) represents any group of characters, the question mark (?) acts as a placeholder for any single character, and the pound sign (#) represents any single number digit. For example, to find all the words that have street in them type *street* in the Find What text box.
Lesson #6
You want to be able to view your data in many different ways. Open the datasheet for the table. The toolbar includes many buttons that you can use to change the way you look at the data. If you want to sort the datasheet based on a specific field, click any record in the field. If your table includes a name field, for example, click on any name and then click the Sort Ascending button on the Table Datasheet toolbar to sort the records in Ascending order by name. To sort the data in descending order you would simply click the field you want to base the sort on and then click the Descending Sort button on the toolbar.
If you want to find a specific item in the database, click the field that you want to search, then click the Find button on the toolbar. The Find and Replace dialog box opens as shown in Figure B-6.
Enter what you are looking for in the Find What text box and then click the Find Next button. The focus will move to the first occurrence of the data you are looking for. Click the Find Next button to keep searching through the records.
Using the Find and Replace dialog box, you can change where the search occurs by clicking the Look in list arrow and selecting a field. You can also improve the quality of the search by specifying where it looks in the whole field or any part of the field. If you want to replace what is found with other text or values, click the Replace tab and then enter the replacement data. You will be prompted for each occurrence or you can replace all occurrences automatically. Be careful when using this option.
Lesson #7
If you want to display only records that meet a certain criteria you can use the Filter command. Criteria are rules or limiting conditions.
To use filter by selection click the cell in the datasheet that has the criteria that you are looking for and then click the Filter by Selection button. For example, if you have a database that includes a pet field, and you want to view all the records that are for canaries. Find any record that has 'canary' in the desired field, then click the Filter by Selection button. The datasheet will display only records that match those criteria. To remove the filter, you simply click the Remove Filter button. The Apply Filter button toggles to a Remove Filter button.
If you want to filter for comparative data or have complex criteria you click the Filter by Form button to open the Filter by Form window as shown in Figure B-7.
Each available field is listed, the grid looks like the datasheet. Filter By Form is more powerful than Filter By Selection because it allows you to use comparison operators. For example, you can find all records that cost more than $25 by typing >25 in the cost field. Comparison operators include:
> Greater Than
>= Greater Than or Equal to
< Less Than
<= Less Than or Equal to
<> Not Equal To
Filter By Form also allows you to enter criteria for more than one field at a time where both criteria must be "true" in order for the record to be shown in the resulting datasheet. You can type the criteria for more than one field before applying the criteria such as finding all products that cost more than a specific value, were due after a certain date and are certain colors. You need to be sure the value is valid in the table.
Lesson #8
A query creates a datasheet of specified fields and/or records from one or more tables. A query datasheet is a subset of data. Queries answer a "question" about the data in your database. You can edit, navigate, sort, find, and filter a query's datasheet just like a table's datasheet. A query is saved as an object within the database.
The Simple Query Wizard helps you choose the tables and fields upon which you base the query. You can select whichever fields you want from any number of existing tables in the database.
Without any limiting criteria, the query datasheet shows you all the records but only the selected fields for that query.
Query Design view lists the fields in the tables that were used to collect the fields in the upper portion of the window, and the fields you have requested for the query in the design grid in lower portion of the screen. Figure B-8 shows the query Design view.
Enter the criteria in the query design grid, specify any sort information in the sort row. You can use the query design grid to sort on more than one field. Click the Datasheet view button to view the results.
Once you view the query results in the datasheet, the sort, filter, and find buttons work the same way in the query datasheet as in the table datasheet.
You can use a query datasheet to edit or add information. The changes will be made to the underlying table object. A query is also commonly used as the source of data for a form or a report.
Lesson #9
Design View is always where you change the structure of the object. You can build a query by using the Query Design View, or use the Query Wizard to help you. If you want to add criteria to limit the number of records that you view in the datasheet, or if you want to change the fields you are viewing, you must use Design View to modify the query. You can open Design view by clicking the Design view button in the Database window or by clicking the Design view button on the toolbar.
To add fields to the query design grid you drag them from the field list. You can drag fields to reorder them in the grid to change how the datasheet will display the fields. You can remove fields from the query design grid you click the field selector and then press delete.
To enter the criteria you work through the design grid to specify the sort order and any limiting factors. If you enter criteria for several fields on one row Access treats it as an "AND" criteria and will include only those records that meet each criteria. If you want to include records that match if any of the criteria meet, enter them on the 'or' row. And is an exclusive search, Or is an inclusive search.
Figure B-9 shows a query design grid that specifies Or criteria for the Category field and a sort order based on the Artist field.
The resulting datasheet is shown in Figure B-10. Records are sorted in ascending order by the Artist field and only Country or Folk records are displayed. The query resulted in 8 records meeting the criteria.
Assignment
Page Access B-23, #3, 20 points. Save and send to me as "People" to steven_radlo@wiu.edu
For Monday, April 9th's class:
Complete lectures Unit C and Unit D. Further enhance these lectures with the "follow along" instructions for Unit C and Unit D in your text book. Personally, I think that Unit B out of your text book is extremely important for understanding databases and will be quite helpful for you when preparing for your final exam (which is Monday, April 23). Before beginning Unit C and D, I just wanted to enhance these lecture notes and your text with a quick pointer about the primary key.
A table's primary key is a special kind of indexed field. Just about every table you create should have a primary key. A primary key helps keep your data more organized. Here are a few tips about using a primary key:
1) A table can have only one primary key. A single table can have lots of indexes, but only one primary key.
2) If you create a new table without a primary key, Access 2000 automatically asks whether you want to add one. If you say yes, the program creates an AutoNumber field at the beginning of your table and sets it as the primary key. If the first field already happens to be an AutoNumber type, Access 2000 accepts it as the primary key without adding anything else to the table.
3) Only certain field types can be keys. Text, number, date/time, and AutoNumber fields all qualify for primary key status. Yes/No, OLE, and memo fields can't be the primary key.
4) Records are automatically sorted by the primary key.
5) The primary key field can be anywhere within your table design. The key can be the first field, the last field, or some field in the middle. Typically though, I recommend putting the key field first in a table... it makes it much easier for you to organize.
The most important thing to remember about choosing which field to be your primary key is in its uniqueness. That is, values in a primary key field must be unique. Access 2000 won't tolerate duplicate key values. Each and every entry in the primary key field must be the only one of its kind. So for your "follow along" instructions in Unit B of your text book, (refer to page Access B-5), "RecordingID" would be the logical choice for a primary key. If it appears that you don't have a field that would be suitable for a primary key, then it is best that you add an AutoNumber field to your table. In the case of "RecordingID" in your text, this field is your primary key and acts as an AutoNumber, generating a unique number for each entry.
Unit C
Lesson #1
A form is an Access database object that allows you to arrange the fields of a record to best meet your data entry needs. Figure C-1 shows a sample form. The form shown in the figure has bound and unbound controls. Form controls include:
Label
Text Box
List Box
Combo Box
Tab Control
Check Box
Toggle Button
Option Button
Bound Image Control
Unbound Image Control
Line and Rectangle Controls
Command button
A form shows the fields of only one record at a time. Since forms are the primary objects used to enter and edit data, you should invest time planning a form. Forms can be built to match a source document to facilitate fast and accurate data entry or forms can be designed to type data directly into the screen rather than first recording it on paper. Before you create a form you should consider the purpose of the form. Then, you need to determine the underlying record source, which will be either a table or query for the form. If there are source documents, you should gather them for analysis. Then you must determine the best control to use for each element of the form. Controls are used to display data in different ways. Controls can be bound or unbound. Bound controls display data from the underlying record source and are also used to edit and enter new data. Unbound controls do not change from record to record and exist only to clarify or enhance the appearance of the form.
Form design considerations include clearly labeled fields and appropriate formatting.
Lesson #2
Form Design View can be used to create a form from scratch. The Form Wizard creates an initial form object that can be modified later if needed. The Form Wizard provides options for selecting fields, an overall layout, a style, and a form title.
The Form Wizard let's you chose whichever fields you want from the underlying objects. You can base a form on a table or query.
When you create a form, you should try to use color to distinguish logical areas. Figure C-2 shows a form that is well designed and takes advantage of many Access form controls.
Forms have descriptive labels help identify the data that appears in text boxes in the form. A field with a Yes/No data type displays as a checkbox on a form. Command buttons can be placed in forms to perform a series of actions.
Forms have navigation buttons to help move through the records. Navigation buttons include information as to which record is displayed as well as the total number of records.
You can quickly create a form by clicking a table or query object in the database window, clicking the New Object list arrow on the Database toolbar, then clicking AutoForm from the New Object menu. AutoForm offers no prompts or dialog boxes; it instantly creates a form that displays all the fields in the previously chosen table or query using the same options as those you chose the last time you used the Form Wizard.
Lesson #3
You can modify the size, location, and appearance of existing controls in Form Design View. Figure C-3 shows Form Design view.
The Toolbox toolbar contains buttons that allow you to add controls to the form. The Field list contains the fields in the record source; the underlying object or objects.
When you work with controls, you first must click to select the control. Black squares, called sizing handles, appear in the corners and edges of the selected control. The mouse pointer shape changes depending on whether you are moving or resizing the control. The shape will change whether you are resizing the control vertically, horizontally, or diagonally. Mouse shapes are as follows:
The left-facing white arrow appears when you are pointing to any non-selected control on the form. It is the default mouse pointer and is used to select a single control.
The dark open hand appears when you are pointing to the edge of a selected control (but not when you are pointing to a sizing handle). Drag this pointer to move selected controls.
The pointing dark hand appears when you point to the larger sizing handle in the upper left-hand corner of a selected control. Drag this pointer to move only the single control where the pointer is currently positioned.
Double-sided arrows pointing horizontally, vertically, or diagonally appear when you point to any sizing handle (except the larger one in the upper left-hand corner), drag these pointers to resize the control in the direction of the arrows.
The text boxes appear as white rectangles in Form Design view. When you move a bound control such as a text box or check box, the accompanying unbound label control to its left moves with it. The field name for the selected control appears in the Object list box. You can use various selection techniques to select more than one control at a time for alignment and formatting.
Lesson #4
When you create a form with the Form Wizard, it places a label to the left of each text box with the field's name. You can modify a label control to be more descriptive or user friendly by directly editing it in Form Design View. There are several ways to select a label including clicking the labels, dragging a selection box, and clicking or dragging the ruler. If you double-click a label, you will open its property sheet. The property sheet is a comprehensive listing of all properties (characteristics) that have been specified for that control. You can make changes directly in the property sheet. The Properties button is on the Form Design toolbar and when clicked, will open the property sheet for the selected control. Figure C-4 shows a label's property sheet.
There are many properties for each control located on the three tabs. The All tab includes all the properties. You may be overwhelmed by all these properties but many of these are determined by Access, by Wizards, or by commands you are familiar with on the menu bar.
If you double-click a label, you will open its property sheet.
Directly editing labels in Form Design View is tricky because you must select the label, then precisely click where you want to edit it. You can also open the label's property sheet and modify the Caption property to change the text displayed by the label control. You can change this to best describe the contents of the text box to make your form user friendly. Be sure to modify the Title label control and not the Title text box controls. Text box controls must reference the exact field name in order to display the data within that field.
Labels on a form can be aligned so that they are closer to their respective text boxes or so they create an attractive form. Use the buttons on the Formatting Form/Report toolbar to change the font style, font size, and font color of labels to enhance the form.
Lesson #5
Text boxes are bound controls that are generally used to display data from underlying fields. You can also use a text box as a calculated control in which case it is not directly bound to a field but rather uses information from a field to calculate a new answer. You can use the Formatting Form/Report toolbar to change the way the text boxes appear on the form. To add a new text box to a form click the Text Box button on the Toolbox toolbar, then click where you want to place the new text box on the form. Figure C-5 shows the toolbox and a new text box added to an existing form.
When you add a new text box, Access automatically adds a new label with a default caption. This will be "Textxx:" where xx is a sequential number based on the number of controls added to the object. You can access the text box's property sheet to bind it to an underlying field or expression, or you can create the calculated expression directly within the box.
To add a calculated control Click Unbound in the new text box, type the expression =[FieldName]-[FieldName]. When referencing field names within an expression, you must use square brackets and type the field name exactly as it appears in the Table Design view. You do not need to worry about upper and lowercase letters. Remember, however, that the label for any expression should be descriptive.
By default, text boxes that contain numeric and currency fields are right aligned. Text boxes that contain fields with other data types or those that start as unbound controls are left aligned. Monetary values should be right aligned and display with a dollar sign and cents. You can use the Text Align property on the text box property sheet to change alignment.
Lesson #6
The tab order is the order in which the focus moves as you press [Tab] or [Enter] in Form View. Tab order determines the order that controls become active as you tab through a form. Since the form is the primary object by which users will view, edit, and enter data, careful attention to tab order is essential to maintain their productivity and satisfaction with the database.
Click View on the menu bar, and then click Tab Order. The Tab Order dialog box opens as shown in Figure C-6.
The Tab Order dialog box allows you to change the tab order of controls in three sections: Form Header, Detail, and Form Footer. To change tab order, you drag fields up or down. You can also click AutoOrder so that Access determines the tab order based on the placement of the fields in the form. If you have moved fields around this is a quick way to get the best order quickly.
Depending on your data entry needs, you may want the focus to shift left and right through the form or down an entire column of fields before beginning again at the top of the second column.
Lesson #7
Forms are used to find, enter, or edit records in a database. To enter a new record, open the form in Form view, and then click the New Record button on the Form View toolbar to display a new, blank record. Type the values you want in each field, using the Tab key to move from field to field through the form.
If you have an AutoNumber field, it will increment automatically when you begin to enter data in a form. If you are editing a record, the Edit Record symbol will appear in the form. See Figure C-7.
If you have a calculated control on a form, changing the values in any of the fields that are used in the expression will result in the calculated field being updated automatically on the form.
Tab order determines the order you move through the form or you can click to go to a specific field. You can the entry in any field. You can use the Find and Find and Replace commands to change data in the underlying table objects. You can also use the Filter by Selection command to find all records that match a specific value displayed in a field in a form. Click in a field that displays the data you want to filter for and then click the Filter by Selection button. You can print filtered records or all the records in a form. Use the Print Preview command prior to printing so you know how long the printout will be.
Lesson #8
Graphic images such as pictures, a logo, or clip art can be added as an unbound image to a form. You can add an image to the Form Header, so that a logo appears at the top of the form in Form View, as well as once at the top of the form printout. To open the Form header you drag the section in Form Design view. Figure C-8 shows an image being inserted into a Form Header.
You need to open the Toolbox and then click the Image button on the Toolbox toolbar. The pointer will look like the image button and you click in the Form Header section where you want the image. Use the horizontal and vertical rulers as a guide for exact placement. When the Insert Picture dialog box opens, navigate to the location of the file you want to insert. Once the file is inserted as an unbound object, you can resize the image to best fill the area.
You can convert an image to a hyperlink by modifying the control's Hyperlink Address property in Form Design view in the property sheet. You can use the image to link a Word document or Excel spreadsheet.
If you want to add a label to the form to give it a title or further explain a process or purpose on the form, click the Label button to add an unbound label.
Unit D
Lesson #1
A report is an Access object used to create printouts. You cannot enter or edit data through a report. Data displayed in a report can be viewed on the screen, but it is usually sent to a printer and the paper copy is then distributed. Access reports are extremely powerful and are the tool used to communicate information. Use the following guidelines when planning a report:
Identify a meaningful title
Determine the fields and records that the report will show
Determine how the records should be sorted and/or grouped
Identify any other descriptive information that should be placed at the end of the report, or at the top or bottom of each page
The Report Wizard can help you quickly create a Report that you can then modify. The Report Wizard asks questions and creates the report, similar to the Form Wizard.
Report sections, as illustrated in Figure D-1 are the Report Header, Page Header, Group Header, Detail, Group Footer, Page Footer, and Report Footer.
Group sections include additional controls that often contain calculated expressions such as subtotals for each group.
Bound text box controls, which generally placed in the report Detail section, are used on a report to display the data stored in the underlying records. Page Header, Page Footer, and Report Footer sections are used for descriptive controls.
Lesson #2
You can create reports in Access in Report Design View or you can use the Report Wizard to help you get started. Click the Reports button on the Objects bar, then double-click Create report by using wizard to open the Report Wizard dialog box as shown in Figure D-2.
The first dialog box asks you which object the report is based on, and which fields from each object you wish to view on the report.
While it is possible to use the first dialog box of the Report Wizard to pull fields from different tables without first creating a query to collect those fields in one object, it is not recommended. If you want to add more fields to the report or limit the number of records in the table, it is very easy to add fields or criteria to an underlying query object to meet these new needs.
Subsequent dialog boxes ask you to determine the style and layout of the report, the Report Wizard also asks how you want report records to be grouped and sorted. Depending on how you respond to the sorting and grouping questions, you will be presented with different levels and amounts of detail information on the resulting report.
Another way to quickly create a report by clicking the New Object button list arrow, and selecting AutoReport. AutoReport does not give you a chance to review the options provided by the Report Wizard.
The Report can open in Preview or Design View. Figure D-3 shows a report in Print Preview.
Lesson #3
Grouping refers to sorting records on a report plus providing an area in which additional controls can be placed. These two sections of the report are called the Group Header and Group Footer. You can create groups on a report through the wizard, or you can change an existing report's grouping and sorting fields in Report Design View. Report Design view shows you the sections of the report as well as the controls within each section.
Click the Sorting and Grouping button on the Report Design toolbar to open the Sorting and Grouping dialog box. Click the Group Header text box, click the Group Header list arrow, click Yes, click the Group Footer text box, click the Group Footer list arrow, then click Yes specifying "Yes" to the Group Header and Group Footer properties, those sections of the report have opened in Report Design View.
Place a text box in the Group Header, if you want it to print once for each new group rather than once for each record. You can add calculated subtotal controls for each group of records by placing a text box in the Group Footer section. Calculated expressions begin with an equal (=) sign.
When creating a calculated expression that uses an Access field, the field name is always surrounded by square brackets and must not be misspelled because it has to reference the exact field name.
You can open the field list to drag a field to a section in the report. The Toolbox is used to add controls. Figure D-4 shows Report Design view with both the Toolbox and Field List open and several form sections.
It is generally more difficult to distinguish between unbound labels and bound text boxes in Report Design View than it was in Form Design view because both types of controls are formatted similarly. The Field List, Toolbox, and property sheet may or may not be visible but can be turned on and off by clicking their respective toggle buttons.
You can directly change the text of a label control, or you can modify its caption property in the property sheet to create a user-friendly report.
Lesson #4
When you further sort records within a group, you order the Detail records according to a particular field. Figure D-5 shows a report sorted in ascending order by the Artist field.
The Report Wizard prompts you for sort information at the time you create the report, but you can also sort an existing report by using the Sorting and Grouping dialog box in Report Design View.
Open the report in Design view, click the field you want to base the report on, click the Sorting and Grouping button on the Report Design toolbar, To select fields you want to sort the records on. Click the Field/Expression list arrow, click the Field or Expression, then specify the Sort order you want in the second column. You can sort either on ascending or descending order. If there is no Sorting and Grouping indicator in the row selector and the Group Header and Group Footer Group Properties are "No" the field is providing a sort order only.
The Sorting and Grouping button is a toggle button; click it to either open or close the Sorting and Grouping dialog box. Use Print Preview to view one or more pages in the report. You can use the Zoom pointer to get a closer look at any detail of the report while in Print Preview.
Lesson #5
An expression is a combination of fields, operators (such as +, -, / and *) and functions that result in a single value. A function is a built-in formula provided by Access that helps you quickly create a calculated expression. Figure D-6 list many common Access expressions, some of which use Access functions.
Notice that every calculated expression starts with an equal sign, and when it uses a function, the arguments for the function are placed in parentheses. Arguments are the information that the function needs to create the final answer. Calculated expressions are entered in text box controls.
If you have an existing expression on a report and want to create a new but similar expression, the best way to avoid errors is to copy the existing expression and then modify it. Be sure that when you copy and paste the expression, you modify the text box control to change the expression.
You may find it useful to take advantage of the Office Clipboard when copying controls. The Clipboard can hold up to 12 items. You can gather controls from various reports and then paste them into a new report for modification.
Use familiar editing techniques for working with bound controls in Design View. You should also change the label to identify the new expression.
Lesson #6
You should arrange the data on the report to create a report that is easy to understand and use. You can align controls in columns and rows. There are several alignment commands available. You can left, right, or center align a control within its own border, or you can align the edges of controls with respect to one another. You can use the same techniques for selecting controls in Report Design View as you did in Form Design view.
Text boxes that display numeric fields are right aligned by default, the labels and text boxes that display calculated expressions are left aligned by default.
Once controls are selected you can click the Align Right button, the Align Center button or the Align Left button on the Formatting (Form/Report) toolbar to display the information right, center, or left aligned (respectively) within the control.
Click Format on the menu bar, point to Align, then select from the alignment options to align the selected controls with respect to one another. The Align command on the Format menu refers to aligning controls with respect to one another. The Alignment buttons on the Formatting toolbar align controls within their own borders. You can also align the right or left edges of controls in different sections on the report.
Figure D-7 shows how you can work to align label and text box controls on a report.
If you work carefully, you can form a perfect column in the Detail section and extend the column up and into the Page Header to create a professional report. Use both the Horizontal and Vertical rulers to guide your work.
Lesson #7
Formatting enhances the appearance of the information in the report. Popular formatting commands, which can be used with either forms or reports, include Bold, italic, underline, alignment, Fill/Back Color, Font/Fore Color, Line/Border Width, and Special Effects.
Figure D-8 lists the buttons on the Formatting toolbar.
While the Report Wizard provides basic formatting on a report, and you can select from different styles and layouts as you complete the Wizard, you often want to change the report's appearance.
Increasing the font size, changing the font style, and applying character formatting such as bold, italics, and underline are common ways to make information more visible on a report. You can also change the colors of the control. You can change the background color of a text box, the border color, or the font color.
Many buttons on the Formatting (Form/Report) toolbar include a list arrow that you can click to reveal a list of choices. When you click the color button list arrows, a palette of available colors is displayed as shown in Figure D-9.
When you print colors on a black and white printer, they become various shades of gray. So unless you always print to a color printer, be careful about relying too heavily on color formatting, especially background shades, as they might become solid black boxes when printed on a black and white printer or fax machine.
Lesson #8
Labels are used for many purposes such as identifying storage folders, contents of floppy disks, and providing addresses for mass mailings. Once the raw data has been entered into an Access database, it is easy to create labels from this data using the Label Wizard.
Click the Reports button, click the New button, click Label Wizard in the new Report dialog box, click the Choose the table or query where the object's data comes from: list arrow, click the table or query that has the fields you want, then click OK
The Label Wizard dialog box opens as shown in Figure D-10 requesting that you specify information about the characteristics of the label.
Creating a label is similar to creating a report. Work through the Wizard and you will see a prototype label to help you determine your choices. The Wizard allows you to change the font, font size, and other text attributes. Larger fonts will be easier to read. Depending on the number of fields, you have to decide what will fit on the label so you can accommodate all the required information.
Any spaces or punctuation that you want on the label must be entered from the keyboard (for example, a space between a Firstname and Lastname field). Also, if you want to put a field on a new line, you must press [Enter] to move to a new row of the prototype label as shown in Figure D-11.
The Label is a report. You can open the Design view and make any additional modifications such as aligning and formatting the controls.
Assignments (Due Monday April 16 by 2:00 pm):
1. Page Access D-22, #2. Save as "Therapist" and send to steven_radlo@wiu.edu (10 pts)
2. Create a table using the following information: (10 pts)
Use the following Field Names:
Last Name:
First Name:
Soc Sec No:
G:
AB:
R:
H:
Declare what field will be your primary key.
Now, put in the following data:
Bailey, Michael 326-54-0183 23 86 17 28
Benson, Carrie 456-98-0976 21 77 21 27
Bristol, Mike 123-45-6969 22 94 16 19
Daly, Scott 987-65-4321 24 109 22 30
Now make a relational database query using the following Field Names:
First Name:
Last Name:
Soc Sec No:
G:
AB:
R:
H:
AVG:
Now sort the batting averages from highest to lowest.
Save your work as "Baseball" and send to steven_radlo@wiu.edu