After Update help with SQL Statement

[RossPart]

Hi,
Using MS Access 365.
Have a small library database. Table 'Titles' includes fields, TitleId and Status. Status is either "On Shelf" or "On Loan"

Have a 'Borrower' Form with details of borrower.
Have 'Borrowings' sub form which shows Borrowing History for Borrower by TitleID This form includes fields 'Date Borrowed' and 'Date Returned'

So far all works well.

So! I want to be able to switch the 'Status' field on the Titles Record, according to whether there is a value in the 'Date Borrowed' but not in the 'Date Returned' field, [On Loan] or, if a Book is returned, [On Shelf].

Borrowings.pdf

My thanks in advance.

[jojowhite]

you can remove the Status field from table Titles but instead use Query to determine if it is On Loan or On Shelf.
create a Query (qryTitleStatus):

SELECT 
         Titles.TitleID, 
         Titles.Title, 
         DCount("1","BorrowHistory","TitleID = " & [TitleID] & " And [Date Returned] Is Null") AS Out, 
         IIf([Out]=0,"On Shelf","On Loan") AS Status 
FROM Titles;

[Welshgasman]

you can remove the Status field from table Titles but instead use Query to determine if it is On Loan or On Shelf.
create a Query (qryTitleStatus):

SELECT 
         Titles.TitleID, 
         Titles.Title, 
         DCount("1","BorrowHistory","TitleID = " & [TitleID] & " And [Date Returned] Is Null") AS Out, 
         IIf([Out]=0,"On Shelf","On Loan") AS Status 
FROM Titles;

O/P might have more than one copy of various titles?

I would have thought there would be a junction table for borrower and title and that table would have the DateBorrowed and DateReturned fields?

[June7]

Might want to look at MS Lending Library template.

[davegri]

LendingLibraryMod-davegri-v03.zip

Here's the Microsoft Lending Library app with many ills cleaned up.
Removed spaces and special char from table names.
Removed lookups in tables (except small value lists)
Removed click events in reports
Renamed table keys (_PK, _FK)
Converted most macros to VBA
Removed the screwy filtering methodology
Removed deprecated methodology
Removed attachments
Removed some unneeded reports
Changed from tabbed docs to overlapping
Adjusted colors in all forms


Some undesirable elements remain such as split forms that cannot be resized in any reasonable manner.

Edit: Replaced v02 DB. Uploaded v03
Replaced split forms with continuous forms - added Edit button to each row
Cleaned up code to conform with changing table IDs from ID to _PK and _FK