Calculated Fields - XI

A calculated field is a field that gets its information from the calculations performed on other fields.  You can build calculated fields in the Query screen by using the addition (+), subtraction (-), multiplication (*) and division (/) operators. 

Expressions
Expressions a combination of functions, field names, numbers, text, and the operators listed above. 

To build an expression to create a calculated field:

• Open an existing query or start a new query
• Click on the View Button
• Click on Design View

• In the Query Pane, right-click on the field where you would like to create the calculation
• Click Build

• Choose the tables that you wish to build the calculation from
• Double-click the field that you want to include in the calculation
• Click the operator that you wish to include in the calculation
• Click the second field you wish to include in the calculation
• Click OK

• Click Run

Zoom
The Zoom Dialog Box allows you to view an entire expression at one time.  To view the Zoom Dialog Box:

• In Design View, right click on the field you want to display
• Click Zoom

Querying a Database - X

A query allows you to select and filter data from multiple tables.  Queries can be saved and utilized as often as you need them.

Query Wizard
The Query Wizard walks you through the steps to set up a query.  To run a query using the query wizard:

• Click the Create tab
• Click the Query Wizard button

• Choose the type of query you wish to run
• Click OK

• Choose the fields you wish to include from each table
• To select fields from different tables, click the Tables/Queries down arrow
• Click Next

Insert pic of query wizard

• Type in a title for the query
• Click Finish
• The query will display

To switch between tables and queries:

• Open the Navigation Pane
• Double click the name of the table or query you wish to view

Query Design Feature
You can also design a query with the  Query Design Button.  To design a query using the Query Design Button:

• Click the Query Design Button on the Create tab

• Select the tables that you would like to query
• Click Add

• Double click the name of the field you would like to query
• Repeat this process for as many fields as you would like in the query

• Click Run

Query Criteria
Query criteria are search conditions used in a query to retrieve specific data.  You can set query criteria to be a specific number or data set, or you can set the criteria to be a range of data.

“value”	Will only display items that are that exact value (replace the word value with what you want to search by)
=	Is equal to
<	Less than
<=	Less than or equal to
>	Greater than
>=	Greater than or equal to
<>	Not equal to
Between  X  And Y	Within a range (replace X & Y with values)
Is Null	Null values
And	True only if both conditions exist
Or	True if either condition exists
Not	True if the single instance is not true

To specify search criteria:

• Click the query that you wish to add conditions
• Type in the appropriate query criteria in the Criteria Box

Managing Data - IX

Add Records to a Table
To add a new record to a table:

• Open the table in Datasheet View
• Click the New Cell
• Type in your new record

Find and Replace
To find data:

• Click the Find button on the Home tab

To find and replace data:

• Click the Replace button on the Home tab

When you are searching for data for a find, replace or go to, you have several options in the Find Dialog Box.  These options are:

Find What Text Box	Type the text you wish to find
Link in Drop Drop-Down List	Use the drop-down list to specify a table or a column to search
Match Drop-Down List	Use the drop-down list to narrow down the search to a field or the beginning of a field
Search Drop-Down List	Use this drop-down to specify the direction to search.
Match Case Check Box	Use this check box to specify whether to search by the same upper and lower case letters.

Totals
The totals button provides you the opportunity to add a totals row to your database.  The total can be the sum, average, a count, minimum, maximum, standard deviation, or the variance.  To set up a totals row:

• Click the Totals button on the Home tab

• Click the down arrow of the cell where you want the totals
• Click the appropriate choice

Sort Records
You can sort records in a datasheet by a single column or by two adjacent columns.  To sort records by a single column:

• Select the field you wish to sort
• Click the Sort Ascending or Sort Descending button

To sort two columns:

• Move the columns to they are adjacent to each other
• Select the desired columns for sorting by holding the shift key and clicking the columns
• Click the Sort Ascending or Sort Descending button

To clear the sort:

• Click the Clear Sort button

Filter
You can filter records to include only records that you want to display.  To filter by a column:

• Open the database in Datasheet View
• Click the down arrow in the field label
• Choose the appropriate filter criteria
• Click OK

To remove a filter:

• Click the filter button on the field label
• Click Clear Filter
• Click OK

Table Relationships - VIII

Table relationships are the associations of data between tables.  By defining table relationships, you can pull records from related tables based on matching fields. 

One-to-One Relationship
A one-to-one relationship is between two tables where the primary key in one table and the foreign key in another table are the same.  For each record in the first table, there is a single matching record in the second table. 

One-to-Many Relationship
A one-to-many relationship occurs between two tables where the primary key in one table can be duplicated many times in another table

Creating Table Relationships
To create relationships between tables:

• Click the Database Tools tab on the Ribbon
• Click the Relationships button

• Click on the Design tab
• Click Show Table

Select the desired tables

• Click Add
• Click Close

• Click the field you wish to create a relationship from
• Drag it to the matching field in the other table
• Click Create

Print a Table Relationship

• Click the Database Tools tab
• Click the Relationships Button
• Click the Relationship Report Button on the Design tab

 

• Click the Print button

Keys - VII

Primary Key
The primary key is a unique identifier for a record.  The primary key cannot be the same for two records.  This field can never be blank.

Composite Key
A composite key is a primary key that is comprised of two or more fields.  It can also be called a compound or concatenated key. 

Foreign Key
A foreign key is a field or combination of fields that are related to the primary key of another table.

Manage Tables - VI

Delete a Table
To delete a table:

• Open the desired database by clicking the Microsoft Office Button and clicking Open
• Right click on a table and choose Delete

Rename a Table
To rename a table:

• Open the desired database by clicking the Microsoft Office Button and clicking Open
• Right click on a table and choose Rename
• Type in the new name

Add a Description to a Table
To add a description to a table

• Open the desired database by clicking the Microsoft Office Button and clicking Open
• Right click on a table and choose Table Properties
• Click the Description text box
• Type in the description

 

Create a Table - V

Table Views
There are two ways to view a table in Access to add data to the table:  Design View and Datasheet View. 

In Design View you can view all the fields with the data types and descriptions.  The records of information that has been added to the database is not viewable. 

To go to Design View:

• Click the down arrow on the View button
• Click Design View

In Datasheet View you can display the records in a table, where one row is one record.  The column headers are the fields you have defined for the database. 

To go to Datasheet View:

• Click the down arrow on the View button
• Click Datasheet View

Adding  New Fields
There are many ways to enter new fields into a database.  New fields can be added in the Datasheet View or in the Design View.

There are two ways to add a new field in Datasheet View:  Add A New Field or the New Field Button. 

To add a New Field within the Datasheet:

• Click the Add New Field column

To add a new field by using the New Field Button

• Click the Datasheet tab on the Ribbon
• Click the New Field Button
• Choose the type of field you wish to add from the Field Templates window

To add a new field in Design View:

• Click the Design View button
• Click on the next available field
• Type in the Name of the field

Data Types
There are many types a data that a field can be predefined to hold.  When you create a new field in a database you should closely match the data type to what will be entered into the field.

Text	Text, number, or a combination up to 255 characters
Memo	Similar to the text field, can contain text, numbers, or a combination up to 2 GB of data.
Number	Numbers up to 16 bytes of data
Date/Time	Date and Time information
Currency	Currency up to 8 bytes and precise to 4 decimal places
AutoNumber	Access creates a unique number for each new record.  This is often the primary key for the table
Yes/No	Yes and No, stored as -1 for yes and 0 for no
OLE Object	Images, documents, graphs up to 2 GB
Hyperlink	Web addresses
Attachment	Attachments such as images, spreadsheets, documents, and charts.

Editing Data Types in Fields
When creating tables, you should define the data types of the tables to most closely match the type of data that will be entered in the field.

To edit the Data Type in Datasheet View:

• Click the field you wish to define
• Click the Datasheet tab on the Ribbon
• Click the down arrow next to Data Type
• Choose the type of data that will be entered into the field

To edit the format of the data:

• Click the field you wish to define
• Click the Datasheet tab on the Ribbon
• Click the down arrow next to Format

To edit the Data Type in the Design View:

• Click Design View
• Click the field name you wish to define or create a new field
• Click the Data Type
• Choose the appropriate Data Type
• Format the field in the Field Properties Dialog box

Create a New Database - IV

You can create a new database from scratch or you can create a database from the database wizard.

New Database
To create a new database from scratch:

• Click the Microsoft  Office Button
• Click New
• Click the New Blank Database icon

• Type in a name for the database
• Click Create

Database Templates
To create a new database from the database templates:

• Click the Microsoft Office Button
• Click New
• Choose the type of database you wish to create

• Type in the name for the database
• Click Create