Home

Tutorial 1 Review

The objectives of Unit 1 are:

1. Define the terms field, record, table, relational database, primary key, and foreign key
2. Open an existing database
3. Identify the components of the Access and Database windows
4. Open and navigate a table
5. Learn how Access saves a database
6. Open an existing query, and create, sort, and navigate a new query
7. Create and navigate a form
8. Create, preview, and navigate a report
9. Learn how to manage a database by backing up, restoring, compacting, and converting a database.

Important Points.

1. Access must be opened first then open the database file. If you click on the database file first (docucentric approach) the database file will open but a number of the wizards will be disabled in Access making it impossible to complete your assignments.
2. When you enter data into a record and proceed to the next record the data is automatically saved to disk.
3. When you save the database, the structure of the database is being saved.
4. When you compact the database, records that have been marked for deletion are removed from the database.
5. A field is a single characteristic or attribute of a person, place, object, event, or idea.
6. A table is a collection of fields that describe a person, place, object, event, or idea.
7. The specific value of a field is called the field value.
8. A collection of related tables is called a relational database.
9. A relationship can be established between tables that have a common field that is of the same data type.
10. A primary key is a field, or a collection of fields, whose values are unique for every record in a table, cannot be null and is the first index of the table. These are the entity integrity restraints of a primary key.
11. When you join the primary key from one table to a common field in another table, the common field in the related table is called a foreign key.
12. A Database Management System (DBMS) is a software program that lets you create databases and then manipulate data in them.
13. A query is a question you ask about the data stored in a database. There are two types of queries. Select queries in which data is selected based on the contents of the fields and do not change the contents of the table. Action queries which change the the contents of the table in some way.
14. When a query is saved the Structured Query Language (SQL) is saved not the dynamic data set created by the query.
15. A form is a graphic user interface into the table.
16. A Form Wizard is an Access tool that asks you a series of questions, and then creates a form based on your answers.
17. A report is a formatted printout (or screen display) of one or more tables in a database.
18. Compacting a database rearranges the data and objects in a database to decrease its file size.
 

Best Practice:

1. Make a backup copy of your database on a daily basic while you are working on it.
a. Right Click on Start button.
b. Select explorer from the pop up menu.
c. Select the drive/folder where your database file is located.
d. Right Click the database file and select copy from the pop up menu.
e. Select the drive/folder where you want to place the copy of your database file.
f. Right click on the drive/folder and select paste from the pop up menu.

Tutorial 2
 

The objectives of Unit two are:
1. Learn the guidelines for designing databases and setting field properties
2. Create a new database
3. Create and save a table
4. Define fields and specify a table's primary key
5. Add records to a table
6. Modify the structure of a table
7. Delete, move, and add fields
8. Change field properties and udate
9. Copy records and import tables from another Access database
10. Delete and change records

Important Points.
1. Rules of Data Normalization: 1st Rule - Eliminate Repeating Groups - Make a separate table for each set of related attributes(fields), and give each table a primary key.
2. When creating a database the first question to ask is what is the output going to be.
3. Identify all of the fields needed to produce the required information.
4. Group related fields into tables.
5. Determine each table's primary key.
6. When more than one field is used as a primary key it is called a composite key.
7. Include a common field in related tables.
8. Avoid data redundancy.
9. Rules for naming fields.

a. A name can be up to 64 characters.
b. A name can contain letters, numbers, spaces, and special characters.
c. A name cannot start with a space.
d. A table or query name must be unique with a database. A field name must be unique with a table, but can be used in other tables.
e. Capitalize the first letter of each word in the name.
f. Avoid extremely long names.
g. Use standard abbreviations.
h. Do not use spaces in field names or object names.

10. The data type determines what field value you can enter for the field and what other properties the field will have.
11. The Field Size property defines a field value's maximum storage size for test, number, and AutoNumber field only.

 

Table 1
Access Field Types

12. The structure of the table can be modified at any time. Fields can be added, deleted, and properties changed.
13. You can copy and paste records from a table in the same database or in a different database only if the tables have the same structure.
14. When you import a table from one Access database to another, you place a copy of the table in the database into which you import it.
15. Updating, or maintaining, a database is the process of adding, changing, and deleting records in database tables.

 

Best Practice: When setting up a database in a company, make sure that you discuss the project with everyone who will be using the database. The executive director will request different data then will one of his subordinates. The key person is the data input clerk. Data integrity must be insured and the data input clerk is the one putting the data into the program. If they are unclear as to their data entry requirements data error will be the rule.
 

Tutorial 3

The objectives of Unit Three are:
1. Learn how to use the Query window in Design view
2. Create, run, and save queries
3. Update data using a query
4. Define a relationship between two tables
5. Sort data in a query
6. Filter data in a query
7. Specify an exact match condition in a query
8. Change a datasheet's appearance
9. Use a comparison operator to match a range of values
10, Use the And and Or logical operators
11. Use multiple undo and redo
12. Perform calculations in a query using calculated fields, aggregate functions, and record group calculations

Important Points.
1. There are two types of Queries: Select Queries and Action Queries
2. Rules of Data Normalization - 2sd Rule - Eliminate Redundant Data - If an attribute (field) depends on only part of a multivalued key, remove it to a separate table.
3. When you use query by example (QBE), you give Access an example of the information you are requesting.
4. The field list contains the fields for the table you are querying.
5. In the design grid, you include the fields and record selection criteria for the information you want to see.
6. The process of relating table is often called performing a join.
7. A one-to-many relationship exists between two tables when one record in the first table matches zero, one, or many records in the second table, and one record in the second table matches exactly one record in the first table.
8. The primary table is the "one" table in a one to many relationship.
9. The related table is the "many" table.
10. Referential integrity rules:

a. When you add a record to a related table, a matching record must already exist in the primary table.
b. If you attempt to change the value of the primary key in the primary table, Access prevents this change if matching records exist in a related table.
c. Cascade update permits the change in value to the primary key and changes the appropriate foreign key values in the related table.
d. Cascade deletes - deletes the record in the primary table and also deletes all records in the related tables that have matching foreign key values.

11. The Relationship Window is used to define relationships (create joins) between tables.
12. Sorting is the process of rearranging records in a specified order or sequence.
13. The sort key is the field used to determine the order of records in the datasheet.
14. Ascending Sort - A to Z, Lowest to Highest.
15. Descending Sort - Z to A, Highest to Lowest
16. A sort key is unique if the value of the sort key for each record is different.
17. A sort key is nonunique if more than one record can have the same value for the sort key field.
18. A filter is a set of restrictions you place on the records in an open datasheet or form to temporarily isolate a subset of the records.
19. Filter by Selection lets you select all or part of a field value in a datasheet or form, and then display only those records that contain the select value in a field.
20. Filter by Form, allows you to select a value from the list arrow that appears when you click any blank field to apply a filter that selects only those records containing that value.
21. A comparison operator asks Access to compare the value in the database field to the condition value and to select all the records for which the relationship is true.

22. An exact match occurs when the value in the specified field must match the condition exactly in order for the record to be included in the query results.
23. Logical Operators are used to combine two or more conditions.

 

Logical Operators
AND, OR, XOR, NOT

AND

OR

XOR

Not

Operator Precedence used in evaluating expressions

 

Best Practice:

Access2003 will let you convert to Access2000 or Access97.  Remember that when you convert the database from 2003 to 2000 or 97,  features supported by the 2003 version will not be available in the 2000 or 97 version.

Tutorial 4
 

Unit four's objectives are:
1. Create a form using the Form Wizard
2. Change a form's AutoFormat
3. Find data using a form
4. Preview and print selected form records
5. Maintain table data using a form
6. Check the spelling of table data using a form
7. Create a form with a main form and a subform
7a. Create a report using the Report Wizard
7b. Check errors in a report
8. Insert a picture in a report
9. Preview and print a report
We will also review Tutorial 1, 2, 3, and 4 for Week 5 Level 1 exam.

Important Point:
1. When creating a form make sure the form is structured exactly like the source document from which the data is being taken.

2. Rules of Data Normalization 3rd Rule - Eliminate Columns not dependent of key. - If attributes do not contribute to a description of the key, remove them to a separate table.
3. The Form Wizard allows you to choose some or all of the fields in the selected tale or query, choose fields from other tables and queries, and display the selected fields in any order on the form.
4. The Find command lets you search for data in a form or datasheet so you can display only those records you wasn't to view.
5. The Find and Replace dialog box allows you to find data and replace it with other data.
6. Wildcard Characters used with find.

7. You can check the spelling of table data using a table or query datasheet or a form that displays the table data.
8. To create a form based on two tables, you must first define a relationship between the tow tables.
9. Based upon a one to many relationship between two tables, the main form will be used for the primary table and the subform for the related table.
10. The Report Wizard ask you a series of questions then creates the report.
11. The set of field values for each position is called a detail record.
12. Each item on a report in Design view is called a control.
13. A bound control is derived from the table directly or through a query.
14. A unbound control is not connected to the underlying table directly or through a query.
15. When printing selected records, select the record(s), file, print, selected records.
16. When printing a selected page of a report, file,print, page 1 to page 5 .
17. In the form or report design view the Error Checking Options button appears any time access identifies a possible error.