Page 1 of 3 123 LastLast
Results 1 to 15 of 34

Thread: Update problems

  1. #1
    Join Date
    Jan 2004
    Posts
    106

    Unanswered: Update problems

    i have created a query that runs off another query called 1a (that calculates how many days late a book is) but when i run the new query it updates all the rows with the same fine and i only want it to update the row of the book number specified by the user. also the query asks for the days late. i want the new query to take the days late as worked out by the 1a query.
    the SQL is below:

    UPDATE LOAN Set Fine=IIf([1a]![Days Late]<7,0,IIf([1a]![Days Late]<14,0.3,IIf([1a]![Days Late]<21,0.6,IIf([1a]![Days Late]<28,1,IIf([1a]![Days Late]<56,1.5,IIf([1a]![Days Late]>56,3))))))
    WHERE [Please Enter Book Number]

  2. #2
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: Update problems

    Originally posted by craig_dixon
    i have created a query that runs off another query called 1a (that calculates how many days late a book is) but when i run the new query it updates all the rows with the same fine and i only want it to update the row of the book number specified by the user. also the query asks for the days late. i want the new query to take the days late as worked out by the 1a query.
    the SQL is below:

    UPDATE LOAN Set Fine=IIf([1a]![Days Late]<7,0,IIf([1a]![Days Late]<14,0.3,IIf([1a]![Days Late]<21,0.6,IIf([1a]![Days Late]<28,1,IIf([1a]![Days Late]<56,1.5,IIf([1a]![Days Late]>56,3))))))
    WHERE [Please Enter Book Number]
    Your problem is your where clause... I'm surprised it updates anything and doesn't just give you an error... You need to add the booknumber field from the 1a query (I'm assuming it's in that query)...

    For example... if the field is called [BookNumber]... you want to update records WHERE [1a].[BookNumber] = what the user enters...
    Try this...

    UPDATE LOAN Set Fine=IIf([1a]![Days Late]<7,0,IIf([1a]![Days Late]<14,0.3,IIf([1a]![Days Late]<21,0.6,IIf([1a]![Days Late]<28,1,IIf([1a]![Days Late]<56,1.5,IIf([1a]![Days Late]>56,3))))))
    WHERE [1a]![BookNumber] = [Please Enter Book Number]
    (changing the name of the BookNumber field to what you have in your query...)

  3. #3
    Join Date
    Jan 2004
    Posts
    106
    sorry, i missed that bit, but it still asks me t enter the days late aswel as the book number.
    it should take the days late from the 1a query

  4. #4
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Originally posted by craig_dixon
    sorry, i missed that bit, but it still asks me t enter the days late aswel as the book number.
    it should take the days late from the 1a query
    It should ask you for the book number parameter... The Days Late thing is a different issue... Are you absolutely positive that the field in query 1a is named exactly "[Days Late]"... Check for spaces or underscores or spelling errors...

  5. #5
    Join Date
    Jan 2004
    Posts
    106
    the first query creates a field called Days Late spelled exactly like that

  6. #6
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Originally posted by craig_dixon
    the first query creates a field called Days Late spelled exactly like that
    Craig... Do me a favour and zip the db and post it here... It'll be much quicker to see it... I have Access 97 and 2000 at work, so one of those formats would be good...

  7. #7
    Join Date
    Jan 2004
    Posts
    106
    good idea, ill make a smaller version cos its about 5mb. i use office XP, will that be ok?

  8. #8
    Join Date
    Jan 2004
    Posts
    106
    here you are. there should be everything you need there. if you need anything else, ill send it
    Attached Files Attached Files

  9. #9
    Join Date
    Jan 2004
    Posts
    106
    have you had a look yet or not?

  10. #10
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Originally posted by craig_dixon
    have you had a look yet or not?
    Yep... I'm not getting the error you mentioned...

    However, I do see what your problem is... You need to uniquely identify which record you're trying to update Craig...

    Think about it... Look at your Loan table... Your LoanID is the only thing that uniquely identifies that record...

    If you ask the user to put a BookNumber in... Say they enter 18.... There are 3 records for that book...

    Then say you even took it further and asked the user to enter StudentID?... Say they enter 2 ...They're still going to get two records in the results...

    If you want the user to select one particular book for one particular student for one particular date borrowed... you'll have to ask them for all of these parameters... Not just the book number...

    Do you want one unique record to be updated?... or all records for one book number?... or???

  11. #11
    Join Date
    Jan 2004
    Posts
    106
    but one thing to do would be to ask teh user for the book number and from that select that book number from the loan table, where [date returned] is null. this is the best way i can think of, but i just dont know how to do this

  12. #12
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Originally posted by craig_dixon
    have you had a look yet or not?
    Further to my last post...

    If you want to update all of the fine fields in the Loan table for one book (ie for book number 18 it would update three rows...)... I've created the update query and am posting it here for you...

    What I needed to do was add the LoanID to query 1a and then create an inner join between the Loan table and query 1a in the update query... Check it out... See if this is what you want it to do...
    Attached Files Attached Files

  13. #13
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Originally posted by craig_dixon
    but one thing to do would be to ask teh user for the book number and from that select that book number from the loan table, where [date returned] is null. this is the best way i can think of, but i just dont know how to do this
    Okay... That should work... Let me update my copy to do this... and I'll repost it... Hang on a minute...

  14. #14
    Join Date
    Jan 2004
    Posts
    106
    hey, thanks a lot! ill have a look

  15. #15
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Originally posted by craig_dixon
    but one thing to do would be to ask teh user for the book number and from that select that book number from the loan table, where [date returned] is null. this is the best way i can think of, but i just dont know how to do this
    Uhhh... Wait a minute... If Date Returned is null how is it going to calculate the difference between the date it was supposed to be returned and the date it was returned... and in turn, the fine??

Posting Permissions

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