Homework answers / question archive / Shelly Cashman Access 2019 | Module 1: SAM Project 1b City Tours Ltd

Shelly Cashman Access 2019 | Module 1: SAM Project 1b City Tours Ltd

MS Access

Share With

---

Shelly Cashman Access 2019 | Module 1: SAM Project 1b

City Tours Ltd.

CREATING AND MODIFYING TABLES AND OTHER DATABASE OBJECTS

GETTING STARTED

?

Open the file

SC_AC19_1b_

FirstLastName

_1.accdb

, available for download from the

SAM website.

?

Save the file as

SC_AC19_1b_

FirstLastName

_2.accdb

by changing the “1” to a “2”.

?

If you do not see the .accdb file extension in the Save As dialog box, do not type it. The

program will add the file extension for you automatically.

?

Open the

_GradingInfoTable

table and ensure that your first and last name is displayed

as the first record in the table. If the table does not contain your name, delete the file

and download a new copy from the SAM website.

PROJECT STEPS

1.

City Tours Ltd. is a company that provides tours in a city that is a popular tourist

destination for travelers. As a customer service manager for the company, you've

decided to create an Access database to better manage information on customers and

tours. Create a new table in Datasheet View with the following options:

a.

Rename the default primary key ID field

TourNumber

and change the data type to

Short

Text

.

TourNumber

should remain the primary key.

b.

Add a new field with the name

TourName

and the

Short Text

data type.

c.

Change the field size of the

TourName

field to

35

.

d.

Add a new field with the name

BasicCost

and the

Currency

data type.

Save the table using

Tours

as the name.

2.

With the

Tours

table still open in Datasheet View, add the record shown in Table 1. Save

and close the

Tours

table.

Table 1: New Record for Tours Table

TourNumber

TourName

BasicCost

W0101

House History

35

3.

Create a new table in Design View to store the customers of City Tours Ltd. who have

requested private group tours as follows:

a.

Add a field with the name

GroupNumber

and the

AutoNumber

data type.

b.

Set

GroupNumber

as the table's primary key.

Save the table with the name

Groups

but do not close it.

4.

With the

Groups

table still open in Design View, add a second field to the table with the

field name

CustomerID

and the

Short Text

data type. Save the table and then close it.

5.

Open the

Guides

table in Design View and change the name of the field named

FName

to

FirstName

and use

LastName

to change the name of the field named

LName

6.

With the

Guides

table still open in Design View, change the data type property for the

GuideID

7.

In Design View, add a new field to the

Guides

table after the

Phone

field. The field should

be named

StartDate

and have the

Date/Time

data type. Save the

Guides

table and do

not close it.

8.

Switch the

Guides

table to Datasheet View, navigate to the fifth record (which has a

GuideID

field value of 108), and

delete

the record.

9.

With the

Guides

table still open in Datasheet View, navigate to the third record (which

has a

GuideID

field value of 104) and change the value to

Devlin

for the

LastName

field.

Close the

Guides

table.

10.

You also need to view individual guide records. Use the

Form

tool to create a form for

the

Guides

table. Save the form with the name

Guide Data Form

and confirm that the

form matches Figure 1. Close the form.

Figure 1: Guide Data Form in Form View

11.

Use the

Simple Query Wizard

to create a query based on the

Customers

table with the

following options:

a.

Include all fields from the

Customers

table in the query.

Save the query with the name

Customers Query

(which is the default name) and

then close the query.

12.

You need to refer to a printed copy of the

Customers

table when reconfirming tour

reservations. Use the

Report Wizard

to create a report based on the

Customers

table

with the following options:

a.

Include the

LastName

,

FirstName

, and

Mobile

fields from the

Customers

table in

the report.

b.

Do not include any grouping levels.

c.

Sort the report by

LastName

in

Ascending

order.

d.

Select

Tabular

as the layout of the report and

Portrait

as the orientation of the

report.

Save the report using

Customer Contact Report

as the name.

Confirm that the

report matches Figure 2 and then close the report.

Figure 2: Customer Contact Report in Print Preview

13.

In the Navigation Pane, use

Customer Data Form

to rename the

Customers

Form.

Save and close any open objects in your database. Compact and repair your database, close it,

and then exit Access. Follow the directions on the SAM website to submit your completed project.

field from Number to

Short Text

.