Results 1 to 5 of 5

Thread: Queries

  1. #1
    Join Date
    Jan 2004
    Posts
    106

    Unanswered: Queries

    I have written 2 queries, the first one finds out how many days late a book is and the second one calculates a fine. the second query runs off the first one

    The SQL for query one is:

    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];

    and the SQL for query two is:

    UPDATE LOAN, 1a SET LOAN.Fine = 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))))))
    WHERE ((([1a].[Book Number])=[Please Enter Book Number]));

    when i run query two it asks for the book number as its supposed to but the updates all the records in the "loan" table and changes the "fine" field to 0.00. i want the query to update only the record with the book number as specified by the user and the correct fine needs to be displayed

    What have i dont wrong? PLEASE HELP!

    I have attatched a screenshot of the design view of the two queries to help:
    Attached Thumbnails Attached Thumbnails 1.jpg  

  2. #2
    Join Date
    Jan 2004
    Location
    Romania - Bucharest
    Posts
    50
    Try changing the WHERE clause in your second query to:

    WHERE ((([1a].[Book Number])=[LOAN].[Book Number]));

  3. #3
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    update table t1
    set t1.value = x
    where t1.id = [param]

    I would recommend creating a function to calculate the fine, which can then be called as an expression an SQL query.
    Last edited by r123456; 02-19-04 at 07:29.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  4. #4
    Join Date
    Jan 2004
    Posts
    106
    thanks for the help but i dont understand the last post. could you please either explain it or paste a code that i can copy? i would appreciate this very much

  5. #5
    Join Date
    Jan 2004
    Posts
    106
    when i run the query now, it updates the table with nothin in it at all. the SQL for query 1 is:

    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];


    and the SQL for query 2 is:

    UPDATE LOAN, 1a SET LOAN.Fine = 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))))))
    WHERE ((([1a].[Book Number])=[Please Enter Book Number]));


    PLEASE HELP ME IM VERY STUCK!

Posting Permissions

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