Library Management System Project in MS Access

The Library Management System will help you:

• Store book details.
• Maintain library member records.
• Record book issue transactions.
• Record book return dates.
• Track available and issued books.
• Calculate overdue fines.
• Search books by title, author, or category.
• Generate book, member, and transaction reports.

1. Open Microsoft Access.
2. Select Blank Database.
3. Enter LibraryManagement.accdb.
4. Select a suitable folder.
5. Click Create.
6. Create the tables described in this lesson.

Use clear and consistent names for tables, forms, queries, and reports.

Create a table named Books to store information about library books.

Steps

1. Open the Create tab.
2. Select Table Design.
3. Add the fields listed above.
4. Set BookID to AutoNumber.
5. Set BookID as the primary key.
6. Save the table as Books.

Create a table named Members to store library member details.

Set MemberID as the primary key and save the table as Members.

Create a table named IssueRecords to track each book issue and return transaction.

Set IssueID as the primary key. BookID and MemberID will connect each transaction to its book and member.

Create a table named Fines to record overdue fines associated with issue transactions.

Save the table as Fines. You can record fines only when applicable, rather than creating a fine record for every issue.

Use the Relationships window to connect the tables.

Relationship Structure

Books.BookID
      |
      | One-to-Many
      v
IssueRecords.BookID

Members.MemberID
      |
      | One-to-Many
      v
IssueRecords.MemberID

IssueRecords.IssueID
      |
      | One-to-Many
      v
Fines.IssueID

Steps

1. Open Database Tools.
2. Click Relationships.
3. Add Books, Members, IssueRecords, and Fines.
4. Drag Books.BookID to IssueRecords.BookID.
5. Drag Members.MemberID to IssueRecords.MemberID.
6. Drag IssueRecords.IssueID to Fines.IssueID.
7. Enforce Referential Integrity where appropriate.
8. Click Create and save the relationship layout.

A book can appear in many issue transactions over time. A member can also have many issue transactions.

The Book Entry form allows library staff to add and edit book information.

1. Select the Books table.
2. Open the Create tab.
3. Select Form or Form Wizard.
4. Choose the fields to display.
5. Select a suitable layout.
6. Save the form as frmBooks.
7. Open the form and test adding a book.

Recommended Forms

• frmBooks — book details.
• frmMembers — member registration.
• frmIssueRecords — issue and return details.
• frmFines — fine management.

Create a form based on the IssueRecords table. Use combo boxes so staff can select a book and a member by name rather than entering numeric IDs manually.

Book Combo Box

1. Open frmIssueRecords in Design View.
2. Add a combo box for BookID.
3. Choose values from the Books table.
4. Select BookID and BookTitle.
5. Set BookID as the bound column.
6. Display BookTitle and save the form.

Member Combo Box

1. Add another combo box for MemberID.
2. Choose values from the Members table.
3. Select MemberID and MemberName.
4. Set MemberID as the bound column.
5. Display MemberName and save the form.

Create a query to display the books that have not yet been recorded as returned.

SQL View

SELECT
    IssueRecords.IssueID,
    Books.BookTitle,
    Members.MemberName,
    IssueRecords.IssueDate,
    IssueRecords.DueDate
FROM
    (Books
    INNER JOIN IssueRecords
        ON Books.BookID = IssueRecords.BookID)
    INNER JOIN Members
        ON IssueRecords.MemberID = Members.MemberID
WHERE
    IssueRecords.ReturnDate Is Null
ORDER BY
    IssueRecords.DueDate;

Steps

1. Open the Create tab.
2. Create a new query and switch to SQL View.
3. Enter the SQL statement.
4. Run the query.
5. Save it as qryIssuedBooks.

The query lists issue transactions with a blank ReturnDate. It does not by itself determine whether a book is overdue.

An overdue book is one that has not been returned and whose due date is earlier than the current date.

SQL View

SELECT
    IssueRecords.IssueID,
    Books.BookTitle,
    Members.MemberName,
    IssueRecords.DueDate,
    DateDiff("d", IssueRecords.DueDate, Date()) AS DaysOverdue
FROM
    (Books
    INNER JOIN IssueRecords
        ON Books.BookID = IssueRecords.BookID)
    INNER JOIN Members
        ON IssueRecords.MemberID = Members.MemberID
WHERE
    IssueRecords.ReturnDate Is Null
    AND IssueRecords.DueDate < Date()
ORDER BY
    IssueRecords.DueDate;

Save the query as qryOverdueBooks. It displays overdue transactions and the number of days past the due date.

For this example, assume the library charges ₹5 per overdue day. You can change the rate to match your library policy.

Example Calculation

Fine Rate = ₹5 per day
Days Overdue = 4

Fine = Days Overdue × Fine Rate
Fine = 4 × ₹5
Fine = ₹20

Calculated Field in a Query

Add this calculated expression to an appropriate query based on the IssueRecords table:

FineAmount: IIf(
    [ReturnDate] Is Null,
    IIf([DueDate]<Date(),
        DateDiff("d",[DueDate],Date())*5,
        0
    ),
    IIf([ReturnDate]>[DueDate],
        DateDiff("d",[DueDate],[ReturnDate])*5,
        0
    )
)

This expression estimates the fine for an unreturned book using today's date, or for a returned book using its recorded return date. It assumes a ₹5 daily rate and no grace period.

The number of available copies can be calculated by subtracting the currently issued copies from the total number of copies.

Concept

AvailableCopies =
TotalCopies - CurrentlyIssuedCopies

For a simple example, suppose the library owns 10 copies of a book and 3 copies are currently issued.

Total Copies: 10
Issued Copies: 3
Available Copies: 7

To calculate this accurately, count IssueRecords rows for each BookID where ReturnDate Is Null. Ensure your issuing process prevents more copies from being issued than the library owns.

Reports present library information in a structured format for viewing and printing.

Recommended Reports

• rptBookList — all library books.
• rptMemberList — registered members.
• rptIssuedBooks — books not yet returned.
• rptOverdueBooks — overdue issue transactions.
• rptFineSummary — assessed fines and payment status.

Steps

1. Select a table or saved query.
2. Open the Create tab.
3. Select Report Wizard.
4. Choose the required fields.
5. Configure grouping and sorting if required.
6. Select a suitable layout.
7. Save the report with a meaningful name.
8. Review it in Print Preview.

A main menu provides a central point for opening forms and reports.

Suggested Layout

LIBRARY MANAGEMENT SYSTEM
--------------------------------
[ Manage Books      ]
[ Manage Members    ]
[ Issue / Return    ]
[ Issued Books      ]
[ Overdue Books     ]
[ Fine Management   ]
[ Library Reports   ]
[ Exit              ]

Steps

1. Create a blank form in Design View.
2. Save it as frmMainMenu.
3. Add a title label and command buttons.
4. Use the Button Wizard or macros to open forms and reports.
5. Test each button in Form View.
6. Set frmMainMenu as the startup form if desired.

1. Create LibraryManagement.accdb.
2. Create Books, Members, IssueRecords, and Fines tables.
3. Set primary keys and foreign keys.
4. Create and test the table relationships.
5. Enter at least 10 books and 5 members.
6. Create book and member entry forms.
7. Create an issue and return form.
8. Create the issued-books query.
9. Create the overdue-books query.
10. Calculate example fines using the agreed daily rate.
11. Create book, member, and overdue reports.
12. Create a main menu form.
13. Test issue and return transactions with sample data.
14. Create a backup of the completed database.

1. What is a Library Management System?
2. Why do we create a Books table?
3. What is the purpose of the Members table?
4. Why are issue transactions stored separately?
5. What is a foreign key?
6. How can you identify books that have not been returned?
7. How do you find overdue books?
8. How can an overdue fine be calculated?
9. How can available copies be calculated?
10. Why is a main menu form useful?