Microsoft Access – Exercise

Part 1

Follow the directions below to create an Address Database. Remember that there is usually more than one way to do something. If you think of a different way to do a task, try it! If it doesn’t work, you can always use the Undo button. Please read through each direction before performing the action.

Starting Access

1. Launch the Access program by double clicking on the Access icon on the desktop

2. In the pane on the right side, click
Blank database
.

3. Click
File Save As Addresses
(Microsoft access will append the file type of “accdb” to your file name.

4. The first thing you see will be the Data Worksheet – looks like Excel.

Creating a Table

1. You already have a Table specified on the left hand side of the screen, called “Table1”

2. Click on the “Click to Add” button at the top of the second column, and select “Short Text” – this is the datatype of the field.

3. “Field 1” is highlighted, so type in

First Name

4. Click on the “Click to Add” button at the top of the third column, and select “Short Text”

5. “Field 2” is highlighted, so type in

Last Name

6. Add the following fields, each as “Short Text” to your table.

Address

City

State

ZIPCode

Phone#

7. Your table should look like this when done:

8. Click on the “x” on the right hand side of the Address tab. Access will ask if you want to save your changes. Click “Yes”, and rename the table to Addresses.

Entering Table Data

1. Double click
Addresses
to open the table we have created. In the table fields, ignore the one labeled ID for now.

2. Press the right arrow key or tab key and enter these field values, pressing enter after each value:

First Name

Last Name

Address

City

State ZIPCode

Phone #

Betty

Bop

123 Apple Way

Houston

TX

77333

(713)555-5555

Daffy

Duck

5492 Hill St

Houston TX

77009

(713)555-9999

Mickey

Mouse

902 East Lucky Ave

Humble

TX

77398

(281)555-5555

3. Click Save and close the table.

4. Note how the ID field is populated as you type.

Creating a Form

1. Click the
Form
tab in the database window.

2. Click
Save
– Access will ask you to name the form – type Addresses

3. In the upper left hand corner there is a “View” tab – click on it, and select “Form View”

Entering Data into a Form

In Access, you can enter data into Tables or Forms. Some find it easier to enter and view data in forms rather than tables. Let’s use the form we just created to enter more data.

1. Click the
Forms
tab in the database window

2. Double click “Addresses” to open the form; make sure you are in Form View.

First Name

Last Name

Address

City

State

ZIPCode

Phone #

Houston

TX

Sponge	Bob	758 Awesome Way	77999	(713)555-0000

3. Click the Save
button.

4. Note that there are two “Addresses” tabs in the database you are working on (on the right in the picture below. Even when you click “Save”, that fourth record does not show in the “Addresses” table view. Hit F5, and it will appear.

Correcting Data

5. Betty Bop is spelled incorrectly. Select the left “Addresses” tab (the table view” above), and change “Bop” to “Boop”

6. Click
Save

Queries

Let’s find the people in our Address Book database that live in Houston. We are planning a last minute birthday party and need to call them immediately. We have four records in our database. If there were more records, it would be time consuming to go through all the addresses. Queries allow you to pluck only certain pieces of data out of your database. Let’s make a query to find the people in our database that are located in Houston.

1. Click the
Create
tab in the database window.

2. Click
Query Wizard,
and select
Simple Query Wizard
and click
OK

3. Click the downward pointing arrow on the right of the Tables/Queries: text box.

4. Select Table:
Addresses
if not already selected.

5. Select
First Name
, Last Name, City, and
Phone#

field values (click once to highlight the selection and then click the right pointing arrow or double click the selection) and move to the Selected Fields side of the window.

6. Click
Next.

7. Under “What title do you want for your query?” type: Houston in the text box and select the
Modify the Query Design
radio button.

8.

At the bottom half of the screen there is a grid. Under the City column and along the Criteria row, we want to type: Houston in that cell. We are making a query, which looks for only those records that include Houston – not the friends that live in Humble, for example.

9. Click the
Run
tool (red exclamation point) on the tool bar to get the results of the query.

10. The records that include Houston are displayed. Now we know who to call and their phone number.

11. Click
Save As [your initials]_assignment8-1a
, upload to Moodle, and close the database window.

12. Close Access.

Congratulations! You have created a Microsoft Access database.

Part 2

1. Do the Challenge! Section at the bottom of the following link, save your file as [your initials]_assignment8-1b and upload to the Moodle shell.

https://edu.gcfglobal.org/en/access2016/working-with-tables/1/

Part 3

1. Do the Challenge! Section at the bottom of the following link, save your file as [your initials]_assignment8-1c and upload it to the Moodle shell.

https://edu.gcfglobal.org/en/access2016/designing-a-simple-query/1/

Part 4

1. Do the Challenge! Section at the bottom of the following link, save your file as [your initials]_assignment8-1d and upload it to the Moodle shell.

https://edu.gcfglobal.org/en/access2016/designing-a-multitable-query/1/