CIS 110 Access Project 1

 

Access Project 1 assignment

Start by creating a new blank database called Project 1. Use the following steps below to complete the project.

1.  Create a new table named Owner using the following structure

Structure of Owner Table

Field Name

Data Type

Field Size

Primary Key?

Description

Owner Code

Text

3

Yes

 

First Name

Text

20

 

Owner First Name

Last Name

Text

20

 

Owner Last Name

Figure 1

 2.  Input the following data into the Owner table

Owner Table Data

Owner Code

First Name

Last Name

A07

Craig

Anders

C89

Jacob

Clark

E

Robert

Foster

F

Guest

Hanley

H

Alan

Kindall

J05

Caroline

Lee

M09

Christine

Moreno

P9

Paul

Riggs

R

Viviana

Roberts

S

Jodi

1

Figure 2

 3.  Create a new table named Rental using the structure listed below

Structure of Rental Table

Field Name

Data Type

Field Size

Primary Key?

Description

Rental Code

Text

3

Yes

Rental Unit Code (Primary Key)

Bedrooms

Number

Long Integer

 

Number of Bedrooms

Bathrooms

Number

Long Integer

 

Number of Bathrooms

Lease Term

Number

Long Integer

 

Minimum Lease Available in Months

Distance

Number

Double

 

Distance in Miles from Campus

Parking

Yes/No

 

 

Does Unit Include a Parking Space

Pets

Yes/No

 

 

Are Pets Allowed?

Rent

Currency

 

 

Monthly Rental Rate

Owner Code

Text

 

 

Code of Rental Unit's Owner

Figure 3

 4.  Input the following data into the Rental table

Rental Table Data

Rental Code

Bedrooms

Bathrooms

Lease Term

Distance

Parking

Pets

Rent

Owner Code

103

3

2

12

3.00

Yes

Yes

1575

A07

106

2

2

9

1.50

No

No

1250

F

109

1

1

6

0.75

No

Yes

950

H

111

4

2

12

2.00

Yes

No

1875

P9

113

2

1

9

1.00

Yes

No

1150

M09

117

2

2

9

1.50

No

No

1200

C89

123

3

2

12

3.00

Yes

Yes

1300

R

134

0

1

3

5.00

No

No

625

E

144

1

1

6

2.25

No

Yes

825

S

151

2

1

12

4.00

Yes

Yes

1172

J05

157

3

2

12

5.50

Yes

Yes

1625

A07

161

4

2

12

7.00

Yes

Yes

1475

M09

163

1

2

12

4.50

Yes

No

775

C89

166

3

2

12

2.00

Yes

No

1100

R

Figure 4

 5.  Add a validation rule to the Lease Term field so that only 3, 6, 9 & 12 can be entered.

 6.  Create Lookup values for the Lease Term field using a Comb Box
      with the following value list 3, 6, 9 & 12.

View Solution

 7.  Create a relationship using the Owners Code between the Owner & Rental tables.
      Set the relationship to both Enforce Referential Integrity & Update Related Fields

View Solution

 8.  Create a Form using the Forms Wizard with
      all fields from the Rental table.
      Use the Columnar layout
      and use the Standard style and save the form as Rentals.

View Solution

 9.  Create a Query named Rentals in the design view including
      the fields Owner Code & Last Name from the Owner table and
      the field Rent from the Rental table.
      Sort the query on the Owner Code field in ascending order.

View Solution

10. Create a Query named Rentals from $1000 to $1500 in the design view including
      the fields Bedrooms, Bathrooms and Rent from the Rental table.
      Sort the query on the Rent field in Ascending order.

View Solution

11. Create a Query named Commissions in the design view including
      the field Owner Code from the Owner table.
      Add a calculated field called Commission where
      the Commission field is 20% of the Rental field value.
      Format the field as Currency.
      Group By Owner Code & Sum the Commission.
      Sort the query on the Rent field in Ascending order.

View Solution

12. Create a Query named Rentals sorted by bedrooms in the design view including the
      Bedrooms, Bathrooms, and Rent fields from the Rental table.
      Sort the data by the Bedrooms field in Ascending order.
      Use Bathrooms as a secondary sorting field and
      Set the sort order to Ascending.

View Solution

13. Create a Report named Rentals using the Report Wizard.
      Use the Rentals sorted by Bedrooms query as the source.
      Select all fields to include in the report.
      Group by Bedrooms and add sorting by Rent in Ascending order.
      Select the Soft Gray format for the report.

View Solution

 

 Grading Rubric

Points

 Requirements

6

Create Owner & Rental tables with correct data

3

Add validation to Lease Term

3

Add lookup values to Lease Term field

3

Create Relationship between tables

3

Create Form - Rentals

3

Create Query - Rentals from $1000 to $1500

3

Create Query - Commissions

3

Create Query - Rentals sorted by bedrooms

3

Create Report - Rentals

30

Total possible for uploading Project 1.mdb

Submit - Upload Your Assignments to the Digital Drop Box