Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2006
    Posts
    213

    Unanswered: Help alter queries

    I have a library database that currently has 2 querys, 1 shows the status of the books and the other shows what reserved books havent yet been returned.

    I want to make a few changes, here is the bookstatus query

    SELECT tblBooks.BookID, tblTitles.ISBN, [BookTitle] & (" ("+[booktype]+")") AS Book, IIf([transtype]="Returned" Or IsNull([transid]),"Free",IIf([transtype]="Loaned",IIf(DateAdd("d",14,[transdate])>=Date(),"Due","***Overdue***"),[transtype])) AS Status, tblTransactions.TransDate, IIf([Status]="Due" Or [status]="***Overdue***",[firstname] & (" "+[mi]) & (" "+[lastname]),"") AS Reader, IIf(nz(Len([reader]),0)=0,"",DateAdd("d",14,[Transdate])) AS DueDate, IIf([status]="***OverDue***",Date()-CDate([DueDate]),"") AS DaysLate
    FROM tblTitles RIGHT JOIN ((tblBookTypes RIGHT JOIN tblBooks ON tblBookTypes.BookTypeID = tblBooks.BookTypeID) LEFT JOIN (tblPeople RIGHT JOIN (tblTransactionTypes RIGHT JOIN tblTransactions ON tblTransactionTypes.TransTypeID = tblTransactions.TransTypeID) ON tblPeople.PeopleID = tblTransactions.PeopleID) ON tblBooks.BookID = tblTransactions.BookID) ON tblTitles.TitleID = tblBooks.TitleID
    WHERE (((tblTransactions.TransID)=(select (SELECT TOP 1 Trans.TransID FROM tblTransactions AS Trans WHERE Trans.TransTypeID<>4 AND Trans.BookID=Books.bookid ORDER BY Trans.TransDate DESC AS TransID FROM tblBooks AS Books WHERE Books.BookID=tblbooks.bookid Or (tblTransactions.TransID) Is Null));


    Here is the reserved books due query:

    SELECT tblBooks.BookID, tblTransactions.TransDate
    FROM tblBooks INNER JOIN tblTransactions ON tblBooks.BookID = tblTransactions.BookID
    WHERE (((tblBooks.BookID) In (SELECT BookStatus.BookID FROM qryBookStatus AS BookStatus WHERE (((BookStatus.BookID)=tblbooks.bookid) AND ((BookStatus.Status)="free" Or (BookStatus.Status)="due" Or (BookStatus.Status) Like "***Overdue***") AND ((BookStatus.TransDate)<=tbltransactions.transdate ))) AND ((tblTransactions.TransTypeID)=4));


    Now there are a couple of problems im having, which is achieving these other goals.

    Is it possible to make the overdue query only show that books that are overdue on an invoice page?

    Is it possible to make each day overdue, times by an amount and give an overall fine amount due on the same invoice?

    You have shown what books are reserved and overdue, but can i show in a parameter query whether a book can be reserved or not?


    Id appriciate some help if anyone can spare any time.
    Thankyou,
    Marley.

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    If you are using the "bookstatus" query for more than one report, then
    change the recordsource on your invoice report to

    SELECT * FROM BOOKSTATUS WHERE STATUS="***OVERDUE***"
    Inspiration Through Fermentation

Posting Permissions

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