Skip to content
Tutorials Center
My learning

Topics

Lesson 34 of 36 ยท Projects and Practice

Project: A Library Database in Access

Build a complete library system in Access. Design tables for books, members and loans, enforce integrity, write overdue and availability queries, then add forms, a report and VBA checks.

In this project you build a small but realistic library loan database: books with several copies, members, and loans with due dates and returns. It uses almost everything from the course: a design process, normalized tables, integrity rules, queries, forms, a report and a little VBA. The SQL runs on this page, so you can test every query; the Access steps tell you where to click.

Step 1: requirements

The library wants to answer these questions:

  1. Which books does each member currently have, and when are they due?
  2. Which loans are overdue, and by how many days?
  3. Is a given title available (how many copies are on the shelf)?
  4. Which are the most borrowed books this year?
  5. A member may borrow at most 3 books at once.

Step 2: tables

Entities: Author, Book (the title), Copy (a physical item, because the library owns several copies of one title), Member, Loan. A book can have several authors, but to keep the project short we use one main author per book.

Table Fields (data type)
Authors AuthorID (AutoNumber, PK), AuthorName (Short Text, required)
Books BookID (AutoNumber, PK), Title (Short Text, required), AuthorID (Number, FK), Isbn (Short Text, unique index), PublishedYear (Number)
Copies CopyID (AutoNumber, PK), BookID (Number, FK), Barcode (Short Text, unique)
Members MemberID (AutoNumber, PK), FullName (required), Email (unique, Ignore Nulls), JoinedOn (Date/Time, default Date())
Loans LoanID (AutoNumber, PK), CopyID (FK), MemberID (FK), LoanedOn (Date/Time, default Date()), DueOn (Date/Time), ReturnedOn (Date/Time, may be empty)

In Access, create each table in Design view, then open Database Tools > Relationships and drag keys: Authors.AuthorID to Books.AuthorID, Books.BookID to Copies.BookID, Copies.CopyID to Loans.CopyID, Members.MemberID to Loans.MemberID. Tick Enforce Referential Integrity on each and leave cascade delete off. A loan with an empty ReturnedOn means the copy is still out; that single field removes the need for a separate "available" flag, which could go stale.

Step 3: build and fill the schema

SQL The library schema with sample data

Press Run (or Ctrl + Enter) to see the result here.

Step 4: queries

Build each in Access with Create > Query Design; switch to SQL View to paste the Access form. The SQLite versions below produce the same results. To keep outputs stable, "today" is fixed at 2025-03-10; in Access use Date().

Q1: who has what right now. A loan with no ReturnedOn is open.

TEXT
SELECT m.FullName, b.Title, l.DueOn
FROM ((Loans AS l INNER JOIN Members AS m ON l.MemberID = m.MemberID)
  INNER JOIN Copies AS c ON l.CopyID = c.CopyID)
  INNER JOIN Books AS b ON c.BookID = b.BookID
WHERE l.ReturnedOn Is Null
ORDER BY l.DueOn;

Access requires nested parentheses for more than two joins; SQLite does not. Q2: overdue loans, with days late (Access: DateDiff("d", l.DueOn, Date()); SQLite: julianday):

SQL Queries: current loans, overdue, availability

Press Run (or Ctrl + Enter) to see the result here.

In Access the date parts become l.ReturnedOn Is Null and DateDiff("d", l.DueOn, Date()) AS DaysLate, and the availability query is a totals query (ฮฃ button) joining Books and Copies with a left join to the open loans. If a query joins on a condition with AND in the ON clause, switch to SQL View, because the design grid cannot show it.

Q4: most borrowed titles, and Q5: the 3-loan limit check that the form will use:

SQL Popularity and the borrowing limit

Press Run (or Ctrl + Enter) to see the result here.

Step 5: forms and the report

Create these forms with Create > Form Wizard and refine in Layout view:

  • frmMembers (single form) with a subform sfrmMemberLoans bound to open loans, linking MemberID as the Link Master/Child field.
  • frmCheckout: a combo box for Member (row source SELECT MemberID, FullName FROM Members ORDER BY FullName, bound column 1, column widths 0";2" to hide the ID), a combo box for available copies (a query listing copies with no open loan), and a Check out button.
  • A report rptOverdue based on Q2, grouped by member, with the days-late column highlighted using conditional formatting and a page footer ="Printed " & Format(Date(), "yyyy-mm-dd").

The checkout button enforces the rules with VBA, so the logic is in one place:

VBA
Private Sub cmdCheckout_Click()
    Dim booksOut As Long
    If IsNull(Me.cboMember) Or IsNull(Me.cboCopy) Then
        MsgBox "Choose a member and a copy.", vbExclamation: Exit Sub
    End If

    booksOut = DCount("*", "Loans", "MemberID = " & Me.cboMember & " AND ReturnedOn Is Null")
    If booksOut >= 3 Then
        MsgBox "This member already has 3 books out.", vbExclamation: Exit Sub
    End If

    CurrentDb.Execute "INSERT INTO Loans (CopyID, MemberID, LoanedOn, DueOn) VALUES (" & _
        Me.cboCopy & ", " & Me.cboMember & ", Date(), Date() + 14)", dbFailOnError
    Me.cboCopy.Requery
    MsgBox "Due back on " & Format(Date + 14, "d mmm yyyy"), vbInformation
End Sub

Private Sub cmdReturn_Click()
    CurrentDb.Execute "UPDATE Loans SET ReturnedOn = Date() " & _
        "WHERE LoanID = " & Me.lstOpenLoans, dbFailOnError
    Me.lstOpenLoans.Requery
End Sub

The combo for available copies uses this row source (the Access form of the left-join query):

TEXT
SELECT c.CopyID, b.Title & " (" & c.Barcode & ")" AS Item
FROM (Copies AS c INNER JOIN Books AS b ON c.BookID = b.BookID)
LEFT JOIN (SELECT CopyID FROM Loans WHERE ReturnedOn Is Null) AS o ON c.CopyID = o.CopyID
WHERE o.CopyID Is Null
ORDER BY b.Title;

Step 6: finishing touches

  • Add a table-level validation rule [DueOn]>=[LoanedOn] (done as a CHECK above).
  • Create a start-up form, hide the Navigation Pane, split the database and deliver an ACCDE.
  • Set up a weekly compact and backup of the back end.
  • Stretch goals: fines for late returns, several authors per book (a junction table BookAuthors), reservations, and a barcode scanner field that opens the checkout form.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why is there a separate Copies table?

    A loan is of a physical copy, not of a title, so copies are their own entity.
  2. 2. How does the design know a copy is currently on loan?

    Deriving availability from the loan rows avoids a flag that could become inconsistent.
  3. 3. Why should cascade delete stay off between Members and Loans?

    History should remain; use an inactive flag for members who leave.
  4. 4. Which clause finds members who hold at least 3 open loans?

    Aggregates are filtered with HAVING after grouping the open loans.

Summary

  • Design: Authors, Books, Copies, Members, Loans, with referential integrity and no cascade delete.
  • Availability and overdue status are derived from open loans (ReturnedOn Is Null), not stored flags.
  • Queries cover open loans, overdue days, availability and popularity; VBA enforces the 3-book limit.
  • Finish with forms, a report, a split, an ACCDE and a backup routine.

Next: Project: order management