Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Jan 2004
    Posts
    106

    Unanswered: date calculations

    in a library database i need to come up with a way of producing a list of book that are overdue for return on 2nd April 2004. could somebody please tell me how to go about doing this, i think i need to use a select query but i dont know how exactly to do it

  2. #2
    Join Date
    Jan 2004
    Posts
    106
    i think i need to find all the records in the "loan" table where [date borrowed] = Is Not Null and [date returned] = Is Null but i dont know how to incorporate the 2nd April 2004 bit!

    PLEASE HELP

  3. #3
    Join Date
    Jan 2004
    Posts
    75
    Need more information than that...

    Do you have a field to identify the date the book was checked out? ex. [checkoutdate]
    Is there a certain amount of time that identifies the book as being overdue? ex. 2 weeks


    If you can provide more information, I'm sure we can help.

  4. #4
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    [QUOTE][SIZE=1]Originally posted by craig_dixon
    i think i need to find all the records in the "loan" table where [date borrowed] = Is Not Null and [date returned] = Is Null but i dont know how to incorporate the 2nd April 2004 bit!

    PLEASE HELP
    I agree with subl - can you just add a little more info regarding what data you are recording for when a book is borrowed - keeping a query simple (love that thought) is the main trick to future work so the way you seem to start off is satisfactory, - you could use for example [date_returned] = Is Null and [date_borrowed] = date() >=-14

    that way you'll get a return if the returned is empty AND the borrowed date is greater or equal to 14 days over due .... for more days to allow a borrow just change the number of days


    Hope this guide helps
    regards
    gareth

  5. #5
    Join Date
    Jan 2004
    Posts
    106
    when a loan is entered, dates are entered into the date borrowed field and a date due back is automatically calculated (1 month ahead). an overdue book is a book that hasnt been returned and should have been by now (todays date is more than date due back).
    i have created a query to find the days late on overdue books. the SQL is below:

    SELECT LOAN.[Book Number], LOAN.[Date Returned], IIf(DateDiff("d",[Date Due Back],[Date Returned])<=0,0,DateDiff("d",[Date Due Back],[Date Returned])) AS [Days Late], LOAN.[Date Due Back]
    FROM LOAN
    ORDER BY LOAN.[Date Due Back];

  6. #6
    Join Date
    Feb 2004
    Location
    Washington
    Posts
    49
    The one thing I don't see here is do you have a field that stores the date that the book was actually returned? If so, you need to compare the actual return date to the calculated date that the book is due. For instance:

    where actual return date is null
    and calculated return date is not null
    and calculated return date < getdate()

    The book is checked out, has not been returned and the due date has passed.
    Cathy

  7. #7
    Join Date
    Jan 2004
    Posts
    106
    i have a field that contains the date the book is borrowed, and then a calculated field containing the date the book is due back. i also have a returned field containing a date when the book was returned, but this will only be filled in when the book is returned. i just dont know how to produce a list of overdue books on THE 2ND APRIL 2004, (i dont know how to include the date, but i think i have to adjust my computers time clock

  8. #8
    Join Date
    Feb 2004
    Location
    Washington
    Posts
    49
    Why is the query checking for a return date in the future?

    Access way:
    where actual return date is null
    and calculated return date is not null
    and calculated return date < 02 Apr 2004

    Sql way:
    where actual return date is null
    and calculated return date is not null
    and calculated return date < '02 Apr 2004'
    Cathy

  9. #9
    Join Date
    Jan 2004
    Posts
    106
    i know it sounds silly but basically i have to produce a list of books that are overdue for return on 2nd April 2004 and i also have to display the current fines. i have a formula for the fines though:

    IIf([Days Late]<7,0,IIf([Days Late]<14,0.3,IIf([Days Late]<21,0.6,IIf([Days Late]<28,1,IIf([Days Late]<56,1.5,IIf([Days Late]>56,3))))))

    this is a bit of a task to test a database and i know that i might have to change the computers time clock

    what would you suggest?

  10. #10
    Join Date
    Jan 2004
    Posts
    75
    Maybe I'm missing something here...

    but why not query for all books where [date returned] is null and [date due back] <= #04/02/04#

  11. #11
    Join Date
    Jan 2004
    Posts
    106
    sorry, im not making myself clear enough. imagine todays date is 2nd April 2004 and you have to write a query that finds all books that havent been returned but should have been (are overdue).

    the current fine also needs to be calculated, but i will worry about that when this part is done

  12. #12
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343

    Lightbulb

    looking at what we're all trying to do is basically a 2 prong issue.

    if a book-is-out is it over due - if yes - how much time & cost-in-fine??

    and

    if a book is out on loan AND is not returned by/on certain-date

    --------------------------------

    The fine OUTSTANDING TIME can only been calc'd after the due date for obvious reasons, therefore a basic query or sql need to work out a >than (X borrow-time) this will highlight the OVERDUE flag...

    then we can work out the time difference in multiples of (y) to work out the fine ( or the threatening letter)

    am i right here??

    gareth

  13. #13
    Join Date
    Jan 2004
    Posts
    106
    a book that is overdue is one that hasnt been returned and the date due back is before todays date. imagine today is 2nd April 2004. i need to produce a list of books that are overdue (havent yet been returned and the date due back is before today) and then calculate the fine that is currently due.

    the fine is based upon how many days late the book is. below is a formula for working out the fine:

    IIf([Late_Qry]![Days Late]<7,0,IIf([Late_Qry]![Days Late]<14,0.3,IIf([Late_Qry]![Days Late]<21,0.6,IIf([Late_Qry]![Days Late]<28,1,IIf([Late_Qry]![Days Late]<56,1.5,IIf([Late_Qry]![Days Late]>56,3))))))

    this formula is based upon the days late, which is calculated from another query (Late_Qry). the SQL for this query is shown below:

    SELECT LOAN.[Book Number], LOAN.[Date Returned], IIf(DateDiff("d",[Date Due Back],[Date Returned])<=0,0,DateDiff("d",[Date Due Back],[Date Returned])) AS [Days Late], LOAN.[Date Due Back]
    FROM LOAN
    ORDER BY LOAN.[Date Due Back];

  14. #14
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343

    Smile

    hi craig

    well it seems that the calculation for this book return is getting complicated.

    I have something like your problem for homework

    in a query i have an issue date (loan date) in the same query i have a calculation submission date (expected_return_date) which issue date + 14

    This automates the date for submission ie issue = 02/02/04...... expected_return_date will show 16/02/04 (i dont need to worry about weekends)

    I then have an Actual_return_date. Using my IFF statement i work out the iff Actual is null then workout todays_date()-expected_date = Over_due_time

    Using this I get 2 things (a) the overdue time (b) when i put the actual date in it stops the penalty being reported

    thats why i like to use the query system over SQL to start then when ive debugged and im happy - convert if i want to

    that help

  15. #15
    Join Date
    Jan 2004
    Posts
    106
    thanks a lot, this really does seem usefull!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •