Library database: a lost leading zero, a duplicate member, a query that lists everything
Lalitpur Community Library has moved its lending register into MS Access. The database has three tables: Books (BookID, Title, Author, Price), Members (MemberID, Name, Phone, JoinDate) and Loans (LoanID, BookID, MemberID, IssueDate, ReturnDate). MemberID is typed by hand as M-001, M-002 and so on. There are currently 60 loan records, of which 22 have no ReturnDate.
After two months the librarian reports three problems. (i) Landline numbers such as 01-5523147 are stored as 15523147 - the leading zero has disappeared. (ii) There are two separate records for a member called Sita Sharma, one with Phone 9841000000 (MemberID M-014) and one with 9841000001 (MemberID M-031), and books have been issued against both. (iii) A query meant to list the four books issued more than 14 days ago and never returned instead lists all 22 unreturned loans. Her criteria row has Is Null under ReturnDate and >14 under IssueDate.
1. Explain why 01-5523147 became 15523147, state the field type and size you would set, and add one measure that stops badly formed numbers being typed in future.
Read the worked answer
The field is a Number field, and a number has no memory of a leading zero - the value 01-5523147 is just 5523147 with a hyphen, so the zero is dropped. A second risk hides here: a Number field set to Integer holds only up to 32,767, so a ten-digit mobile number would overflow; anyone fixing this by choosing Number needs Long Integer, which is still the wrong answer for a telephone number.
Set Phone to Short Text (Text in older versions) with Field Size 10–11. Text keeps the leading zero and can keep the hyphen, and - the decisive point - nobody ever adds, subtracts or averages telephone numbers, so there is no arithmetic benefit in storing them as numbers.
To stop bad entry, add an Input Mask such as 00-0000000 for landlines or 0000000000 for mobiles, or a Validation Rule like Is Null Or Len([Phone])=10 with a validation text message. The mask prevents the error at the keyboard; the field type alone only prevents it after the fact.
2. Explain what a primary key on MemberID would have prevented, and describe how the two Sita Sharma records should be merged without losing loan history.
Read the worked answer
MemberID should be the primary key of Members: unique, never blank, one value per member. With that key set, Access refuses a second row with the same MemberID at the moment of entry, so M-031 could never have been created as a separate Sita.
The merge, in order:
- Take a copy of the .accdb file first - this is the step people skip and regret.
- Choose the surviving ID, say M-014, after checking which phone number is current.
- In Loans, update every row where MemberID = M-031 to M-014.
- Delete the M-031 row from Members.
The relationship Members(MemberID) → Loans(MemberID), one-to-many, with referential integrity enforced, is what makes this safe: with cascade updates the loans follow the key change automatically, and with integrity on, Access will refuse to delete a member that still owns loan rows. Without the relationship the delete would succeed and leave orphan loans pointing at a member who no longer exists.
3. Why does the overdue query return all 22 unreturned loans? Rewrite the criteria so it returns only the four books issued more than 14 days ago and never returned.
Read the worked answer
Two faults combine. First, >14 under a Date/Time field is not 'fourteen days ago'; Access coerces 14 into a date serial - a date in January 1900 - so the test becomes 'issue date later than 14 January 1900', which every loan passes. Second, the direction of the comparison is wrong even in principle: an older loan has a smaller date value, so 'more than 14 days ago' needs <, not >.
The corrected criteria, on the same row of the design grid so that they are ANDed:
- IssueDate:
<Date()-14 - ReturnDate:
Is Null
In SQL: SELECT * FROM Loans WHERE ReturnDate IS NULL AND IssueDate < Date()-14;
Two points worth stating: Date() is a function and must not be in quotes - writing "Is Null" or "Date()-14" searches for that literal text and returns nothing; and criteria on the same row are AND, while criteria on different rows are OR, which would give 'all unreturned books OR all old loans' and is exactly the over-listing she is already seeing.