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

 

Field Types
Auto Number A number that is assigned automatically and never changes again.
Currency Dollar amounts
Date/Time Dates and times
Hyper Link Hyper Link address HTTP, FTP, E-mail, .doc, .etc..
Lookup Values that come from another table, a query, or a list of values you supply.
Memo Can contain numbers or letters up to 64,000 including spaces
Number Byte , Integer, Long Integer, Single precision, Double precision. Decimal,Replication ID.
OLE - object Picture, sound, or video
Text Can contain numbers or characters up to 255 bytes including spaces.
Yes/NO Can contain Boolean values (yes/no, true/false, on/off) Can contain only one of two values
 

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.

 

 

> greater than A > B
< less than A < B
>= greater than or equal to A >= B
<= less that or equal to A <= B
= equal to A = B
<> not <> A
Between..And between two values (inclusive) Between 50 and 325
In( ) in a list of values In("Hall","Seeger")
 
Like matches a pattern that includes wildcards *, ? like "707*"
 

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

 

A B C Result
True True True True
False True True False
False False True False
False False False False
 

OR

 

A B C Result
True True True True
False True True True
False False True True
False False False False
 


XOR

 

A

B C Result
True True True False
False True True True
False False True True
False False False False
 


Not

 

A Result
True False
False True

24. A calculated field is a field that displays the results of an expression and can be used in a query, form or report. The calculate field does not appear in the database.
25. An expression can contain a combination databse fields, constants, numeric and logical operators.  

Operator Precedence used in evaluating expressions

 

High ^ Arithmetic
  Unary + or -  
  * / \ mod  
  + -  
  <, >, <=, >=, =, <> Relational
  NOT Logical
  AND  
Low OR and XOR  

26. Parentheses () can be used to change operator precedence.
27. The Zoom box is a large text box for entering text.
28. The Expression Builder is an Access tool that contains an expression box for entering the expression, buttons for common operators, and one or more lists of expression elements, such as table and field names.
29. Aggregate functions perform arithmetic operations on selected records in a database.

 
Aggregate Function Determines Data Type Supported
AVG Average of field values Autonumber,Currency, Date/Time, Number
Count Number of records selected Autonumber,Currency, Date/Time, Number, Memo, OLE,Text, Yes/No
Max Highest field value Autonumber,Currency, Date/Time, Number,Text
Min Lowest field value Autonumber, Currency, Date/Time, Number, Text
Sum Total of field values Autonumber, Currency, Date/Time, Number
StDev Standard Deviation of Variance Number, Currency
Var Variance of Data Number, Currency

30. The Group By operator divides the selected records into groups based on the values in the specified field. 

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.

 

Wildcard Character Purpose Example
* Match any number of characters th* or *ent or be*ter
? Match any single character a?t find art or ant or apt
[] Match any single character within brackets a[fr]t find aft and art
! Match any character not within brackets a[!fr]t finds act,ant, but not aft or art
- Match any of a range of characters must be A to Z. a[d-p]t finds aft,ant apt, but not act or art.
# Match any single numeric character #72 finds 072,172,272, etc.
 

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.