Homework answers / question archive / Illustrated Access 2016 | Module 1: SAM Project 1b Scholarships Database CREATING A TABLE AND BUILDING A RELATIONSHIP GETTING STARTED ? Open the file IL_AC16_1b_ FirstLastName _1

Illustrated Access 2016 | Module 1: SAM Project 1b Scholarships Database CREATING A TABLE AND BUILDING A RELATIONSHIP GETTING STARTED ? Open the file IL_AC16_1b_ FirstLastName _1

MS Access

Share With

---

Illustrated

Access 2016 | Module 1: SAM Project 1b

Scholarships Database

CREATING A TABLE AND BUILDING A RELATIONSHIP

GETTING STARTED

?

Open the file

IL_AC16_1b_

FirstLastName

_1.accdb

, available for download

from the SAM website.

?

Save the file as

IL_AC16_1b_

FirstLastName

_2.accdb

by changing the “1” to

a “2”.

o

Hint

:

If you do not see the

.accdb

file extension

in the Save As file dialog

box, do not type it. Access will add the file extension for you automatically.

?

Open the

_

GradingInfoTable

table a

nd 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.

Create a new table in Design View as follows:

a.

Add a field with the name

ApplicationID

and the

AutoNumber

data

type.

b.

Set the

ApplicationID

field as the table’s primary key.

Save the table with the name

Applications

, but do not close it.

2.

With the

Applications

table still open in Design View, add a second and third

field to the table using the field names

FirstName

and

LastName

,

respectively. Both fields should have the

Short Text

data type.

3.

In Design View, add a fourth field to the

Applications

table using the field name

StudentID

and the

Number

data type.

4.

In Design View, add a fifth field to the

Applications

table using the field name

AppDate

and the

Date/Time

data type.

5.

In Design View, add a sixth field to the

Applications

table using the field name

ScholarshipID

and the

Number

data type. Save the

Applications

table, then

switch to Datasheet View.

6.

In Datasheet View, enter the two records shown in Table 1 on the next page into

the

Applications

table. Save and close the

Applications

table.

Table 1: New Records for Applications Table

ApplicationID

FirstNam

e

LastName

StudentID

AppDate

ScholarshipID

1

Mindy

Rose

444

9/1/2017

4

2

Joshua

King

555

9/2/2017

7

7.

Open the

Scholarships

table in Design View. Change the name of the field

named

Name

to

ScholarshipName

, then change the name of the field named

Amount

to

ScholarshipValue

.

8.

With the

Scholarships

table still open in Design View, change the data type

property of the

Description

field to

Long Text

.

9.

In Design View, add a new field to the

Scholarships

table after the

Major

field.

The field should be named

Renewable

and have the

Yes/No

data type. Save

and close the

Scholarships

table.

10.

Create a new relationship in the database as described below:

a.

Open the Relationships window, then add the

Applications

table to it.

Close the Show Table dialog box if it is open.

b.

Create a one-to-many relationship between the

Scholarships

and

Applications

tables using the common

ScholarshipID

field.

c.

Enforce referential integrity on the relationship. Save and close the

Relationships window.

11.

Open the

Scholarships

table in Datasheet View. Find, select, and delete the

record with the

ScholarshipID

field value 12 (and the

ScholarshipName

field

value Great Plains Associations) from the

Scholarships

table. (

Hint

: Click the

Yes

button when warned that you are about to delete one record, and click

Yes

again if you are warned about potential data loss.)

12.

With the

Scholarships

table still open in Datasheet View, navigate to the record

with the

ScholarshipID

field value 20 (and the

ScholarshipName

field value

Alpha Kappa Alpha), then change the

ScholarshipName

field value to

Alpha

Kappa Alpha Graduate School Scholarship

.

13.

With the

Scholarships

table still open in Datasheet View, navigate to the record

with the

ScholarshipID

field value 53 (and the

ScholarshipName

field value

ScholarshipPoints), then change the

DueDate

field value to

9/1/2018

. Save and

close the

Scholarships

table.

14.

Open the

Majors

table in Datasheet View, then expand the subdatasheet for

Computer Science. (

Hint

: You should see five related records from the

Scholarships

table.) In the subdatasheet, navigate to the record with the

ScholarshipID

field value 55 (and the

ScholarshipName

field value Intertech

Foundation Scholarship), then change the

ScholarshipValue

field value to

$20,000.00

. Close the

Majors

table.

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

using the Compact and Repair Database button on the Database Tools tab of the Ribbon,

close it, then exit Access. Follow the directions on the SAM website to submit your

completed project.