Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

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 Borrowers 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

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_2

Step: 3

blur-text-image_3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Database Reliability Engineering Designing And Operating Resilient Database Systems

Authors: Laine Campbell, Charity Majors

1st Edition

978-1491925942

More Books

Students also viewed these Databases questions

Question

Define and describe the sections in a job description.

Answered: 1 week ago

Question

Discuss the relationship between job analysis and HRM processes.

Answered: 1 week ago