Thread: Help alter queries
08-13-06, 16:39 #1Registered User
- Join Date
- Feb 2006
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.
08-14-06, 12:24 #2Village Idiot
- Join Date
- Jul 2003
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