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

    Unanswered: calculation problems

    i have written a quewry to find the fine of an overdue library book but it doesnt work. the SQL of the query is as follows:

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

    the query runs off a select query (1a) that finds out how many days late a book is and cretes a temporary field (days late). the problem is that the query says it is about to update 671 records (when there are onlyabout 30 in the database) and then the field that it is supposed to be updating is left blank. there is nothing wrong with the first query, that runs fine.

    craig_dixon1986@hotmail.com is my email if you can help or reply here. Thanks!

  2. #2
    Join Date
    Oct 2003
    Location
    New York
    Posts
    23
    it would appear that one record could fall into several of your nested iif statements. i.e. if a book is 6 days overdue, it would be <7, <14, <21..etc.

    You may want to specify ranges...between # and #...or try reversing your logic...using the greater number of days first...

  3. #3
    Join Date
    Jan 2004
    Posts
    106
    i never thought about that, thanks a lot!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, the logic is fine -- if the days late is 6, then the first test is true, the result is 0, and the other tests are never reached

    what's missing is the join criterion

    the example at mysql.com for updating one table from another is --

    UPDATE items,month SET items.price=month.price
    WHERE items.id=month.id;

    your query for updating one table from another is --

    UPDATE LOAN, 1a SET LOAN.Fine = something


    no WHERE clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2004
    Posts
    106
    i have no idea what all of that means but thanks anyway, ill try it!

  6. #6
    Join Date
    Jan 2004
    Posts
    106
    i arent updating one table from another though, im just updating a table based upon the results of another query

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you is?

    what are LOAN and 1a if not tables?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2004
    Posts
    106
    LOAN is a table but 1a is a query that works out how many days late the book is
    The SQL for the query 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];

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    first of all, my apologies for bringing mysql.com into the discussion

    i'm sorry, usually i'm very good about knowing which forum i'm posting in, and obviously i screwed up this time

    but you still have a syntax problem, right?

    the general syntax to update one table from another in access is
    PHP Code:
    update table1
    inner 
      join table2 
        on table1
    .keyfld table2.keyfld
       set table1
    .fldx table2.fldy
     where table2
    .fldz 'foo' 
    so in your case you would have something like:
    PHP Code:
    update LOAN
    inner
      join 1a 
        on LOAN
    .[Book Number]
         = 
    1a.[Book Number
       
    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.[Days Late] > 
    note the WHERE clause to limit the rows being updated to only those where the book is late


    by the way, your nested Iifs will return NULL if days late is exactly 56
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jan 2004
    Posts
    106
    dont forget that 1a is a select query, not a table, will the code still work?

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, it should, you can use a saved query the same as a table

    in other databases, you'd use a view
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jan 2004
    Posts
    106
    thanks for the code, but an error appears saying:

    Sytax error (missing operator) in query expression 'LOAN.[Book Number]=1a.[Book Number]'

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    shot inna dark, try renaming 1a to something that doesn't start with a number
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jan 2004
    Posts
    106
    why, how will this help?

  15. #15
    Join Date
    Jan 2004
    Posts
    106
    right, ive tried this but it only updates 16 records, when there are 26 records in the table. the ones it did update were totally random. why is this?

Posting Permissions

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