var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
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.
firstname.lastname@example.org is my email if you can help or reply here. Thanks!
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...
i never thought about that, thanks a lot!
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
your query for updating one table from another is --
UPDATE LOAN, 1a SET LOAN.Fine = something
no WHERE clause
i have no idea what all of that means but thanks anyway, ill try it!
i arent updating one table from another though, im just updating a table based upon the results of another query
what are LOAN and 1a if not tables?
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]
ORDER BY LOAN.[Date Due Back];
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
so in your case you would have something like:
on table1 . keyfld = table2 . keyfld
set table1 . fldx = table2 . fldy
where table2 . fldz = 'foo'
note the WHERE clause to limit the rows being updated to only those where the book is late
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 ] > 0
by the way, your nested Iifs will return NULL if days late is exactly 56
dont forget that 1a is a select query, not a table, will the code still work?
yes, it should, you can use a saved query the same as a table
in other databases, you'd use a view
thanks for the code, but an error appears saying:
Sytax error (missing operator) in query expression 'LOAN.[Book Number]=1a.[Book Number]'
shot inna dark, try renaming 1a to something that doesn't start with a number
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?