Results 1 to 5 of 5

Thread: Query Problems!

  1. #1
    Join Date
    Jan 2004
    Posts
    106

    Unanswered: Query Problems!

    i have writen two queries that ask the user for a book number and then calculate a fine based upon how many days late the book is for return query one is a select query that works out how many days late the book is and query2 is an update query, based upon query and, that calculates the fine and stores that value i teh fine field of the loan table

    the only problem is that the querys ask teh user for a book number and then update all the records in the fine table and give them all the same fine (the fine that is only for the book number that has been selected)

    PLESE HELP

    i have attatched a screen shot of the design veiws of the two queries
    Attached Thumbnails Attached Thumbnails 1.jpg  

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Update table
    set fine = fineQuery
    where user_id = 'user_id' AND book_id = '[book_ID_Param]';
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Jan 2004
    Posts
    106
    what does that mean and what will it do? where do i pu that lot?

  4. #4
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    My advice Craig... is to put the Fine field in the first query and get rid of the parameter in that first one... Show 'em all at this point... You don't need it filtered right now... In the second query you add a join... inner join the Loan table and query 1 on BookNumber... Then add the parameter for booknumber... When you try to run the query it should prompt you for the book number... and filter the recordset to be updated...

    HTH

    If you want more help, could you give us the SQL for each of the queries? Thanks!

  5. #5
    Join Date
    Jan 2004
    Posts
    106
    SQL for first query:

    SELECT LOAN.[Date Returned], DateDiff("d",[Date Due Back],[Date Returned]) AS [Days Late], LOAN.[Book Number]
    FROM LOAN
    WHERE (((LOAN.[Book Number])=[Please Enter Book Number]));


    SQL for second query:

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

Posting Permissions

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