Need serious help before a final exam...

[kduznetsov]

This exam will not include SQL, so I need things the default way they're done..

Firstly I basically need a query run to write if a book is in a "Circulation" table, then it is not availible. Ive tried various "IIf" but none of them do the job, because it's in two different tables. It does have a "date" field so I tried to make it say if date is null, then the book is Availible, but to no avail.

[alansidman]

Why don't you post your tables and the fields for each. Then post the SQL statement for your query that you have attempted let us see what it looks like.

It is tough to visualize without seeing the layouts.

Alan

[kduznetsov]

http://i56.tinypic.com/xftfn9.jpg

[June7]

You need to do a join query between Inventory and Circulation tables. Jointype 'show all from Inventory...' Then in the criteria row under DateOut or Circulation.ObjectID fields you can put Is Null - to show all books not checked out or - Not Is Null - to show books that are checked out. However, I see possible issue with this structure. Will Circulation table retain record for every time a book is checked out, in other words, history of activity for each book? This means one-to-many relationship and the simple join query will not serve. You will have to first query the Circulation table for the latest checkout for each book. This is a Totals (or aggregate) query using Max function on the DateOut field then join that query to the Inventory table.

[kduznetsov]

SELECT Inventory.ItemID, Inventory.Category, Circulation.DateOut, IIf([Circulation.DateOut]>"5/21/11","No","Yes") AS BookOut
FROM Inventory INNER JOIN Circulation ON Inventory.ItemID = Circulation.ItemID;

This is the SQL, what exactly is wrong with my IIf statement, book out turns into #Error..

[alansidman]

Ok, I think I have it. In a query, join your Inventory.ItemID in a left join to Circulation.ObjectID. Put them both in your QBE grid and set the criteria for the Circulation.ObjectId to "IsNull" This will then list everything in your inventory that is available for circulation.

Alan

[kduznetsov]

Thanks, the following query worked:
SELECT Inventory.ItemID, Inventory.Category, Circulation.DateOut, IIf(isNull(Circulation.DateOut),"No","Yes") AS BookOut
FROM Inventory LEFT JOIN Circulation ON Inventory.ItemID = Circulation.ItemID;