Additional Caluculations from things from other tables!

[CCFC TIL I DIE]

Hi

I was wondering if anyone could give me any advice on this problem. It's to do with a school with library and one table has the date due back for a book and the actual date returned.

From this fines need to be calculated. They are

These are up to and including

7 Days 30p
14 days Additional 30p (60p)
21 days Additional 40 p (100p)
28 days Additional 50p (150p)
56 days Additional 150 p (300p)

Any help would be much appreciated

 

[Dugantrain]

The first thing that you're going to want to do is to filter for all books that have been returned past the due date and then determine, in days, how late each book is. You would do this in a Query; Criteria for your [Date Returned] field will be:

>[Date Due Field]

Now, you need to define a new field in that query which will calculate how many days late the book is. The DateDiff function is very handy for this:

Days_Late:DateDiff("d",[Date Due Field],[Date Returned Field])

You now want to base another query off of this query in order to Calculate the fines. You will be defining a new field and using nested Iif functions:

Fine_Due:Iif([Days_Late] between 1 and 7, 30,Iif([Days_Late] Between 8 and 14, 60,Iif([Days_Late] Between 15 And 21,100,Iif([Days_Late] Between 22 And 28, 150, 300))))

You didn't set a fine for over 56 days late, so the previous expression will calculate anything over 28 days as a 300p fine. Taylor to your needs.

Note that there are several ways to go about doing this; I'm thinking that a better way (long-term) would be to have a look-up table of days late and corresponding fines; that way if the fine amounts ever change, then you can Update it in a table rather than having to change code around in a query. Also, this query is not storing these values in a table, which you may eventually want to do. Just some things for you to consider.

 

[CCFC TIL I DIE]

Hi

Thanks for your help but im trying to get the price to be depending on the number of days overdue. E.G. A book is 8 days late the price will automatically appear as 30p. Different books have different number of days overdue Would it be better to do another table with a list of the prices as I was thiking of this

Thanks

 

[CCFC TIL I DIE]

jsut had a go and it doesn't seem to like having more than one iff query at a time

 

[CCFC TIL I DIE]

Finally cracked it with a bit of common sense!