i am in the process of creating a basic library borrowing system using access this is a project.
i have created my tables, as well as some forms and have ran some queries.
i would like my system to calculate when a book is overdue, and how much will have to be payed.
what do i use to do this queries??? VBA???
any help greatly appreciated
************************************************** ************************************
Dear Nelly2007
I got to know this forum today and I have admired it. This is my first attempt to respond and I’m not an expert on MS Access but let me try to reply to your questions in a simple way:
From your closed thread I got more information helped me to relpy:
1. How to automatically display in a form, when a book is overdue
Step 01: in the Loan table in addition to the DateDue you need to have a flag to determine those who returned their books from others who don’t yet did that. The field can be a flag (Yes/No type). Let us call it Returned.
Step 02: Using the design view you can create a query (Let us called QryDateDue) based on your three tables to show the student Name, Book Details, date loaned, date due and Returned.
Step 03: Add an expression in the criteria grid under the field Date Due as follows <Date(). The Date() function will return the date of today. The whole expression will help you to get those who are overdue. It is imperative that your computer date and time zone are correct.
Step04: Add an expression in the criteria grid under the field Returned as follows No. This will exclude the records of those who already returned their books in the past. This is important because obviously the DateDue in the records of those who returned their books is overdue but the difference that they returned their books.
Step 05: You can create a report and base it on the mentioned query in step 01. The report will show those who are overdue.
2. How to calculate how much the fine is going to be
In this situation, the assumptions and the ways are many. Here after is one way which is defiantly is not perfect hoping others may contribute with a better solutions.
Assumptions for this scenario are as follows:
A01: You are using the same fine for all books i.e. the book title have nothing to do with the amount of the fine per week.
A02: Sine it is not advisable to hardcode the fine price in a query grid you may need to create a table containing the prices for different fines. For example you may create the following fields in the tblFine: FineOneW, FineTwoW, FineThreeW, FineFourW, and FinalFinePrice as the last and biggest fine. This will enable you to edit the fine price from time to time using a form based on the created table
Step 06: Assuming the two scenarios were fulfilled, you can go back to your query QryDateDue in the design view and modify it by adding the table tblFine without linking it to any of the three tables.
Step 07: Create a calculated field which will calculate the fine for each week delay. To do this in the query grid and in an empty column/field type the following equation Fine: IIf((Date()-DateDue])<=7,1*[FineOneW],IIf((Date()-[DateDue])<=14,2*[FineTwoW],IIf((Date()-[DateDue])<=21,3*[FineThreeW],IIf((Date()-[DateDue])<=28,4*[FineFourW],[FinalFinePrice]))))
The above equation is calculating the delay in weeks but it can be modified easily to be in a daily base if needed.
Step 08: you can create report based on the query to group and display those who are over due and sum up their fines if they were borrowed more than one book a time.
I hope this will help especially English language is not my first language.
Thank you and best regards
2
