45 min read
Library Capstone
Build the full Riverside library database: four tables, keys, relationships, RI, validation, sample data, two queries, a member form, and a city-grouped report.
This lesson is a guided checklist. Work through it in order on a real Riverside Public Library file in Microsoft Access — or use the playgrounds here to rehearse each check.
When every box passes, your RiversideLibrary.accdb should have Members, Books, Loans, Fines, the relationship lines, rules, qryLondonMembers, qryUnpaidFines, MembersForm, and MembersByCityReport.
Small example data for learning. A real library database may have thousands of records.
Step 1 — Create Four Tables
Create each table in Design View. Primary keys are AutoNumber. Foreign keys on Loans and Fines are Number (Long Integer).
| Four tables to create | |
|---|---|
Goal
Create the Members table in Design View.
Every other object reads from tables. Start with the member list.
Open or create the database
Do this
Verification
Check your work
You are correct if all of these are true:
- Members appears in the Navigation Pane. Design View shows MemberID as the primary key.
If something looks wrong
Fix
Every field needs a name. MemberID must be marked as the primary key before you save.Practice
Select the primary key column.
| Members | |||
|---|---|---|---|
Step 2 — Relationships and Referential Integrity
Goal
Draw the three relationship lines and turn on referential integrity.
Loans and Fines store foreign keys. RI stops orphan links.
Open Relationships
Where
Do this
Verification
Check your work
You are correct if all of these are true:
- Three lines connect the tables. Each line shows 1 on the parent side and ∞ on the child side.
Target relationship diagram
The bar marks the ONE side (the primary key). The three lines mark the MANY side (the foreign key). The line joins those two fields — it is not a flow of data.
Click a key icon, a field name, or a relationship line to highlight the matching fields.
Practice
Connect Books.BookID to Loans.BookID.
Drag from a key on the right of one table onto a key on the left of another. Delete a line with Backspace.
Step 3 — Validation and Required Fields
On Members in Design View:
- FullName — Required = Yes
- City — Validation Rule:
In ("London","Manchester","Bristol","Leeds") - Phone — Short Text; optional input mask for a UK mobile shape
On Fines:
- Amount — Validation Rule:
>=0 - Paid — Yes/No
Try to save Yrk in City — Access should refuse it.
Practice
Step 4 — Enter Sample Data
Enter the course sample rows in Datasheet View (or your own matching set):
- Members — five members, including two in London (Sophie Walsh and Amelia Jones)
- Books — titles such as The Secret Garden and Oliver Twist
- Loans — mix of returned and open loans (ReturnDate blank on open loans)
- Fines — at least one unpaid fine linked to an open loan
| Members | |||
|---|---|---|---|
| Loans | ||||
|---|---|---|---|---|
| Fines | ||||
|---|---|---|---|---|
Step 5 — Two Queries
Create and save these select queries:
- qryLondonMembers — members where City = "London". Show MemberID, FullName, City, Phone.
- qryUnpaidFines — fines where Paid = No. Show FineID, LoanID, Amount, FineDate.
Run each query. qryLondonMembers should return 2 rows (Sophie Walsh and Amelia Jones). qryUnpaidFines should return FineID 1 only.
Goal
Create qryLondonMembers in Query Design.
A saved query reads current table data each time you run it.
Start Query Design
Where
Do this
Verification
Check your work
You are correct if all of these are true:
- The result shows two London members.
Practice
Swipe or use the arrows to move between questions.
1 / 2
Step 6 — MembersForm
Goal
Create a form for entering members.
Forms show one record at a time and reduce typing mistakes.
Select Members
Where
Do this
Verification
Check your work
You are correct if all of these are true:
- The new member appears in the Members table with the next AutoNumber.
Step 7 — MembersByCityReport
Goal
Create a report grouped by city with member counts.
Reports format data for printing. Grouping piles members by city.
Select Members
Where
Do this
Verification
Check your work
You are correct if all of these are true:
- London shows Count = 2. Bristol, Manchester, and Leeds each show Count = 1.
Step 8 — Final Verification
Before you call the file finished:
- [ ] Four tables exist with correct keys and data types
- [ ] Three relationship lines with Enforce Referential Integrity
- [ ] City validation rejects Yrk
- [ ] qryLondonMembers returns 2 rows; qryUnpaidFines returns FineID 1
- [ ] MembersForm adds a row to Members
- [ ] MembersByCityReport groups by city with counts
- [ ] File → Info → Compact & Repair Database on a backup copy when done
Independent Challenge
Work these checks without scrolling back to the guided steps. Use what you know about keys, links, and query criteria.
Practice
Swipe or use the arrows to move between questions.
1 / 5
More lessons in Microsoft Access · Next: Course Exam · Previous: "How Do I...?" Quick Index