Answered step by step
Verified Expert Solution
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 LibraryMasterDatabase.accdb
ABLMember.xlsx
ABLReturn.txt
Complete the following steps using any technique you wish.
Open the database file, ABL LibraryMasterDatabase.accdb. Change the title of the database to
ABL Library Books for Everyone.
Note: The tblBooks and tblBorrowers tables are already populated with data.
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
No
Yes
No
MemberLastname Short Text Last name of the member
Field Size
Required
Allow Zero Length
Indexed
No
Yes
Yes Duplicates OK
MemberStatus Short Text using Lookup
Wizard create the
following dropdown list
Admin, Active, Blocked,
Unknown
What is the current status of the
member?
Field Size
Required
Allow Zero Length
Indexed
No
Yes
No
Import and append data from ABLMember.xlsx in the tblMembers table.
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 DateTime Return date for the book borrowed
Format
Required
Indexed
Short Date
No
No
ActReturnDate DateTime 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 ABLReturn.txt in the tblReturns table.
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.
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.
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.
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.
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 to
Revert to the original date.
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.
Create the following report:
rptBorrowers, based on the qryBorr
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started