Instructions

Create a new folder, titled, Access Exam Folder in your personal folder or on your desktop. Save the

files from the Practical Component page to your Access Exam Folder:

$$ ABL Library$\_$Master$\_$Database.accdb

$$ ABL$\_$Member.xlsx

$$ ABL$\_$Return.txt

Complete the following steps using any technique you wish.

$1.$ Open the database file, ABL Library$\_$Master$\_$Database.accdb. Change the title of the database to

ABL Library $$ Books for Everyone.

Note: The tblBooks and tblBorrowers tables are already populated with data.

$2.$ Create the following tables and fields:

a$.$ tblMembers

Field Name Data Type Description

MemberID Number A unique ID for a member

Field Size

Required

Indexed

Long Integer

Yes

Yes $($No Duplicates$)$

MemberFirstName Short Text First name of the member

Field Size

Required

Allow Zero Length

Indexed

$25$

No

Yes

No

MemberLastname Short Text Last name of the member

Field Size

Required

Allow Zero Length

Indexed

$25$

No

Yes

Yes $($Duplicates OK$)$

MemberStatus Short Text $($using Lookup

Wizard create the

following drop$-$down list $$

Admin, Active, Blocked,

Unknown

What is the current status of the

member?

Field Size

Required

Allow Zero Length

Indexed

$255$

No

Yes

No

$$ Import and append data from ABL$\_$Member.xlsx in the tblMembers table.

$3$

b$.$ tblReturns

Field Name Data Type Description

ReturnID AutoNumber A unique ID for a returned book

Field Size

Indexed

Long Integer

Yes $($No Duplicates$)$

BorrowID Number A unique ID for a borrower

Field Size

Required

Indexed

Long Integer

No

No

BookID Number Unique ID for the book

Field Size

Required

Indexed

Long Integer

No

Yes $($Duplicates OK$)$

ReturnDate Date$/$Time Return date for the book borrowed

Format

Required

Indexed

Short Date

No

No

ActReturnDate Date$/$Time Date the book was actually returned on

Format

Required

Indexed

Short Date

No

No

LateDay Number Number of days that the book is

submitted late

Field Size

Required

Indexed

Long Integer

No

Yes $($Duplicates OK$)$

Fine Currency Fine levied for late book submission

Required

Indexed

No

No

$$ Import and append data from ABL$\_$Return.txt in the tblReturns table.

$3.$ Create the following forms with the given properties using the Form Wizard:

a$.$ frmBooks

Record Source tblBooks

Caption frmBooks

Default View Single Form

Form Header $$ Name frmBooks

Form Header $$ Caption Books and Borrowers

Allow Form View Yes

Allow Datasheet View Yes

Allow Layout View No

$$ Extend the form header across the width of the form.

$4$

$$ Add all the fields from tblBooks and place them one below the other.

$$ Disable the ID field.

b$.$ frmBookAuthor

Use the Form Design command for this activity.

Record Source tblBooks

Caption frmBookAuthor

Default View Single Form

Allow Form View No

Allow Datasheet View Yes

Allow Layout View No

Add the Title and Author fields from tblBooks in this form.

For the Title field:

$$ Label: TitleLabel

$$ Caption: Book Title

$$ Control Source: Title

For the Author field:

$$ Label: AuthorLabel

$$ Caption: Book Author

$$ Control Source: Author

c$.$ frmBorrowers

Use the Form Design command for this activity.

Record Source tblBorrowers

Caption frmBorrowers

Form Header Name Borrowers

Form Header Caption Borrower$$s Details

Default View Single Form

Allow Form View Yes

Allow Datasheet View Yes

Allow Layout View No

$$ Add all the fields from tblBorrowers and place them one below the other.

$$ Align them to the left of the form.

$$ Increase the width and depth of the form.

$$ Widen the form header across the width of the form.

$$ Add a subform in the frmBorrowers form with the following properties. Close the subform

wizard and manually define the following subform properties using the Property Sheet.

$5$

o Name: frmBookAuthor

o Source: frmBookAuthor

o Label Name: BookAuthorSubformLabel

o Label Caption: Books and Their Authors

o Link Master Fields: BookID

o Link Child Fields:ID

$$ Extend the caption across the width of the subform.

$$ Widen the fields to accommodate the data.

$4.$ Create relationships between the following tables:

$$ ID in tblBooks to BookID in tblBorrowers

$$ MemberID in tblMembers to MemberID in tblBorrowers

Enable Enforce Referential Integrity for the relationships.

$5.$ For the tblBorrowers table add a Validation Rule and Validation Text corresponding to the following

message in Datasheet View using the Fields tab: The return date cannot be earlier than the borrow

date.

Check the validity of the rule by changing the return date for the record with

MemberID $2489$ to $8/7/2021.$

Revert to the original date.$$

$6.$ Create the following query:

$$ qryBorrowers, where the borrow status is featured using the following fields:

From tblBorrowers: BorrowID, BorrowDate, Status.

From tblMembers: MemberFirstName, MemberLastName.

Sort the query in a Descending order for the BorrowID field.

$7.$ Create the following report:

$$ rptBorrowers, based on the qryBorr