Access Review Projectpage 1 of 8

Access Review ProjectPage 1 of 8

North Greenville University

CSCI-1305 Intro to Information Technology

Access Skill Enchancement Document 1-2

Access has the look and feel of other Microsoft Office products, including its layout and navigational aspects. That is where the similarity ends. Access is a database and, more specifically, a relational database where relationships can be established between tables of data in the database.

Tables are the backbone and the storage container of the data entered into the database. If the tables are not set up correctly, with the right relationships, then the database may be slow, give you the wrong results or not react the way you expect.

An Access database is made up of tables, queries, forms, and reports. All the data is stored in tables. Queries, forms, and reports allow you to change how you view the data, manipulate the display of the data, and create printed reports. Tables, queries, forms, and reports are considered Access objects.

By following the instructions below, you will begin by starting a database and creating tables.

Getting Started

Open Access

Click Blank desktop database to start a new file

Name the file: My Practice Database

Click Create

Save and Name a Database Object

Right click the Tab for the un-named table (Table1)

Click Save

Name it: Volunteers

Click OK or press Enter

Opening the Access program and creating a blank database will open one new table you could begin building. The previous steps had you create a database and name the table. Next, you will create a structure for the database by naming the fields of information you expect to add to the table. Fields will appear in the columns of Datasheet view, with the field name appearing at the top of the column.

Create a Table in Datasheet View

Click the drop-down arrow to the right of Click to Add

Select Short Text

Type: First Name

Press Enter

Click the drop-down arrow to the right of Click to Add

Select Short Text

Type: Last name

Press Enter

Click the drop-down arrow to the right of Click to Add

Select Short Text

Type: Age

Press Enter

Observe the structure of the table you have created and the fields or columns where you would enter data

Modify the Data Type for Age by click the Age heading to select

On the Fields Ribbon, Formatting Group, click the drop-down arrow to the right of Data Type

Select Number

Close the table by clicking the X close button in the upper right

Observe the name of the table in the Navigation pane to the left side of the screen. This area will display all the objects you create.

Open the Table is Datasheet View

In the Tables Section of the Navigation Pane, double-click the Volunteers table

Observe that the table opened in Datasheet View

Click in the First Name blank space and type a name, then press Enter

Type information for two or three rows of data

Observe that the table has been set up to AutoNumber

Switch to Design view

In the Tables Section of the Navigation Pane, right-click on the Volunteers Table

Select Design View

Observe the structure of design view and the Field Properties task pane below

Click the X Close button to close the table

You will continue to work with tables and will discover that you have choices for how you view the table structure. In every type of Access object, you will have more than one way to view the object.

Create a Table in Design View

Next, you will create a table by using Table Design View. In design view, you are creating the field headings for your data, and determining what type of data will go in each column. Columns are called “fields” in Access. You do not add data in Table Design View. Instead, you are creating the structure and rules of the table. You will find many additional property settings can be applied by using Design View.

On the Create Ribbon, Tables Group, click Table Design

Create a table structure with the following information

Field Name / Data Type
CustomerID / Number
FirstName / Short Text
LastName / Short Text

On the exam, when you are instructed to create a table in Design View, you will need to know to go to the Create Ribbon and select Table Design.

Set a table’s primary key in Design view

Select the row that contains CustomerID by clicking the small gray space to the left

On the Design Ribbon, Tools Group, click Primary Key

Click the X Close button to close the Table, answer Yes to saving

Name the Table: Customers

Click OK or press Enter

After setting a Primary Key, duplicates will not be allowed in the field. Observe the navigation pane on the left side of the program. You can see the names of the existing objects.

Switch between Datasheet View and Table Design View

Double-click the Customers Table to display in Datasheet View

Datasheet view is a layout of columns and rows. You might compare this view to what you would expect to see in an Excel file. At this point, the Customers Table does not have any data.

On the Home Ribbon, Views Group, click the drop-down arrow on the View Button

Select Design View

You are noticing there is always more than one way to do things. To switch views, you can right-click the object tab, right-click the object name in the navigation pane, double-click the object name, and use the View button on the ribbon. We are back to design view. You will find several methods of switching views and saving objects.

In Design view, you have a number of options that allow you to further define the rules for the data to be entered into the fields. Observe the Field Properties list toward the bottom of the screen.

Add fields to a table in Design View

 Click on the blank space below LastName and add the following information to the table:

Field Name / Data Type
City / Short text
State / Short Text
Comment / Short text
Zip / Short Text
DOB / Short Text
Payment / Number
Phone / Short Text

Change a Field Data Type in Design View

Select the Comment Field by clicking in the Comment row of the Data Type column

Click the drop-down arrow for Data Type

Change the Data Type to Long Text

Short Text data type allows for up to 255 characters of text in the field. Long Text will allow you to type in paragraphs of information.

Set a Default Value for a Field in Design View

A default value is a value that will automatically be entered into the field for each new record. You can change the value, however, if you know there will be many of a certain entry, setting a default value will save time and effort.

Select the Zip field by clicking in the Zip space of the Field Name column

Look to the Field Properties section below click in the space to the right of Default Value

Type: 29688

Press Enter

Change the Field Size Property in Design view

Select the State field by clicking in the State row under the Field Name column

Look to the Field Properties below and double-click in the space to the right of Field Size to select the value of 255

Type: 2

Press Enter

This setting will only allow 2 digits for the State.

Rename a Field in Design view

Select the Comment Field in the Field Name Column

Double-click the word Comment to select the word

Change the Name to: Notes

Press Enter

Move a Field in a Table in Datasheet View

Double-click the Customers table in the Navigation Pane to switch to Datasheet View

Answer Yes to saving

Observe the Notes column heading has changed from Comments to Notes

Click the heading for Zip

Drag and drop to position between State and Notes

Enter Records in a Datasheet View

In the Customers Table, Datasheet View, Double-click in the space under Customer ID to select the value

Type the following information, pressing the Enter key between entries

When you are typing the entries, observe that the Zip has a default value of 29688. You designated that earlier. If your database has many duplicate entries, it may be useful to set up a default value. You can change the entry when necessary.

ID / FirstName / LastName / City / State / Zip / Notes / DOB / Payment / Phone
1 / James / Smith / Tigerville / SC / 29688 / Call before June / 2/1/79 / 200 / 8645551234
2 / Sandy / Allen / Charlotte / NC / 28201 / Set up for discount / 12/15/82 / 250 / 8645556666
3 / Mike / Jones / Tigerville / SC / 29688 / Call before July / 3/1/92 / 150 / 8645551111

Resize a Datasheet Column to Best Fit

Click the heading for Notes to select the column

Position the mouse in the heading area on the dividing line to the right of Notes and look for the double arrow symbol

Double-click to autofit

Hide Columns in Table Datasheet view

Right-click the heading for Notes

Select Hide Fields

Right-click any other heading and select Unhide Fields

Check Notes

Click Close

Rename a Field in Table Datasheet view

Right-click the heading for City

Select Rename Field

Type: Town

Press Enter

Move a Field in a Table in Design View

Right-click the Customer Tab and select Design view

Click in the small gray area to the left of the Notes Field Name to select the row

Release the mouse button

Place the mouse pointer in the selected gray area to the left of Notes

Drag to position the field below Zip

Change a Field’s Data Type in Design view

Click the drop-down arrow in the Data Type column for DOB

Select Date/Time

Next, we will format the way the date is displayed. In order for you to have choices for formatting dates, you must apply the Date/Time Data Type to the field.

Set a Field’s Format Property in Table Design View

Click to select the “DOB” Field

In the Field Properties section below, click in the space to the right of Format

Click the drop-down arrow to the right side

Select Medium Date

Double-click Customers in the Navigation Pane to switch to Datasheet View

Answer Yes to saving

You will observe the data format has changed to the Medium Date format.

Set a Field’s Validation Rule Property in Table Design View

On the Home Ribbon, Views Group, click the View Button

Select Design View

Click to select the “Payment” Field Name

In the Field Properties list, click in the space to the right of Validation Rule

Type: >100

Press Enter

In this case, the Validation Rule establishes a rule that will only allow a number that is greater than 100. We will imagine that the minimum payment allowed is greater than $100. The Validation Rule has many possibilities and will help you preserve the integrity of your database.

Set a Field’s Validation Text Property in Table Design View

Click to select the “Payment” Field

In the Field Properties list, click in the space to the right of Validation Text

Type: Payment must be greater than $100

Press Enter

Should someone attempt to enter a value that is not greater than 100, the Validation Text would appear.

Set a Field’s Input Mask Property in Table Design View

Click to select the “Phone” Field

In the Field Properties list, click in the Input Mask space

Click the small button to the right side

If necessary, answer Yes to saving

If necessary, answer Yes to “Data integrity rules have been changed…” In some cases you will need to answer No

In the Input Mask Wizard, select Phone Number

Click Next

Click Next

Select: With the symbols in the mask

Click Next

Click Finish

Use an Input Mask to control data entry in an Access database. For example, you can force users to enter a phone number with an area code or an employee identification code with 2 letters followed by 3 numbers.

Create a Lookup field Using a List of Values

When there will be a limited number of correct entries that should be entered for a field in a record, you can set up a Lookup. That means you will be presented with a list of possible entries and can choose the appropriate entry. Creating a Lookup saves time during the input process, and also helps to preserve the integrity of you database by only allowing the correct entries.

Click in the State Field Name to select the field

In the Data Type column for State, click the drop-down arrow and select Lookup Wizard

Select “I will type in the values that I want.”

Click Next

Under Col1 type: SC

In the next space down type: NC

In the next space down type: GA

Click Next

Allow State to be the name

Click Finish

In this scenario, we will imagine you only have customers from SC, NC, and GA. Creating a Lookup limits your choices to those three states. It is also possible to check the setting that will ONLY allow those entries, so a different entry would never be allowed.

Use a Lookup field to Enter Data into a Field

Double-click the Customers Table to switch to Datasheet View

Answer Yes to saving

Double-click the CustomerID field on the next blank row to select the value of 0

Type the following:
4DannyMooreAtlanta

Click the drop-down arrow under State to use the Lookup

Click the drop-down arrow and select GA, press Enter

Continue typing: 30303 Call before June2/15/87 90

Press Enter after typing 90

Notate the Validation Rule text for the minimum payment amount and click OK

For the Payment Type: 150

For the Phone Type: 8645550000

Press Enter

Observe the Phone Number Input Mask

Close the Customers Table

Answer Yes to saving if necessary

This activity has given you exposure to working with Tables in Access. You can have many tables that you use to build queries, forms, and reports. You store all your original data in tables. Completing the “Skill Enhancement Files” will demonstrate every skill you will need for the exam. You can repeat this activity as many times as you feel you need for understanding the skills.