Validation/expression

[Cool J]

HI there, I currently have a database which records book
Loans.

I am creating a Loan query which currently has the
fields:

Student Name
Book Number
Loan Date
Loan Due
Loan Returned
Days Late
Fine Amount

What expression or validation could I use so only
books not on loan can be borrowed. Where would I add this?

and

What expression could I use to prevent a book from being
renewed more than twice by the same student. How do I
actually do this step by step?

Thanks

 

[Robin Proctor]

You really need a normalised database of 3 tables, representing the 3
entities Students, Books and Loans.

1) tblStudent: student_ID(PK), student_Name, etc
2) tblBook: book_ID(PK), book_No, book_Title, etc
3) tblLoan: loan_ID, loan_student_ID(FK), loan_book_ID(FK), loan_Date,
loan_Due, loan_Returned, loan_Days_Late, loan_Fine, etc

You would create one-to-many relationships between the PK-FK field pairs.

A query to show books available for loan might be something like:

SELECT * from tblBook
WHERE book_ID NOT IN
(
SELECT loan_book_ID FROM tblLoan
WHERE loan_Returned IS NULL
)


You could prevent a book being borrowed more than once by the same
student by creating a unique index on the fields loan_student_ID,
loan_book_ID in tblLoan.

Robin Proctor