library check in and out

B

bookworm

i would like to create a database of books that I lend out. THe process
should work wherein someone can see what books are available and if they take
one, they have the ability to check it out, and when they return, check it
back in. It would all be in the honor system, so there are no fees involved
or password security. All lenders will be able to access this file from a
shared directory.
 
J

John Vinson

i would like to create a database of books that I lend out. THe process
should work wherein someone can see what books are available and if they take
one, they have the ability to check it out, and when they return, check it
back in. It would all be in the honor system, so there are no fees involved
or password security. All lenders will be able to access this file from a
shared directory.
Click to expand...

This is a pretty straightforward Access application. I'd suggest using
three tables:

Books
BookID << perhaps ISBN, shelf number, or an Autonumber; just needs
to be a unique ID for each physical book
Title
Author << or you could have an Authors table and a BookAuthor many
to many link table if you want to be thorough
<other fields about the book>

Borrowers
BorrowerID << probably autonumber
LastName
FirstName
<any other info about the person>

Loans
BookID <<< what got borrowed
BorrowerID <<< by whom
CheckoutDate <<< date/time or just date, whichever you need
CheckinDate <<< ditto


You could use a Form based on a Query selecting just those books which
are available:

SELECT BookID, Title, Author, <other fields>
FROM Books LEFT JOIN Loans ON Loans.BookID = Books.BookID
WHERE BookID NOT IN
(SELECT BookID From Loans WHERE CheckinDate IS NULL);


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
B

bookworm

Thank you. I tried doing as you suggested but since I am relatively
inexperienced in access, I am stumped at your query structure below. I set up
the tables just fine. I appreciate your help
 
J

John Vinson

Thank you. I tried doing as you suggested but since I am relatively
inexperienced in access, I am stumped at your query structure below. I set up
the tables just fine. I appreciate your help
Click to expand...

The query was "air code" - I *think* it's right but I'm not certain.
Try creating a new Query; don't add any tables at all though. Select
the SQL window (with the leftmost dropdown tool in the query design
toolbar, or View... SQL) and copy and paste the SQL code into that
window. Edit any field or tablename changes in that window.

Then try switching back to the query grid to see how Access displays
it, and/or to datasheet view to see if it returns the records you
expect.

If you've got specific problems you might want to post a new thread -
I'm going to be pretty much tied up over the weekend; one of the other
volunteers will be glad to help but they might not jump in on a
continuing discussion.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
K

Ken Snell [MVP]

John Vinson said:
On Fri, 17 Dec 2004 22:49:01 -0800, "bookworm"


If you've got specific problems you might want to post a new thread -
I'm going to be pretty much tied up over the weekend; one of the other
volunteers will be glad to help but they might not jump in on a
continuing discussion.
Click to expand...

bookworm -

I'll monitor this thread during the weekend if you have additional
questions/problems. John has helped me in similar manner in the past, so
I'll return the favor.
 
B

bookworm

Thank you Jen and John,

Happy Holidays! So I used the code you provided in an SQL statement and here
is the message I get when I click on the query:
"The specified field 'BookID' could refer to more than one table listed in
the FROM clause of your SQL statement"
 
J

John Vinson

Thank you Jen and John,

Happy Holidays! So I used the code you provided in an SQL statement and here
is the message I get when I click on the query:
"The specified field 'BookID' could refer to more than one table listed in
the FROM clause of your SQL statement"
Click to expand...

Well, the error message explains the problem; you have two tables both
of which contain fields named BookID, and the program can't tell which
table's instance of BookID you mean. My error! I should have
explicitly referenced the table. Try:

SELECT [Bookss].BookID, Title, Author, <other fields>
FROM Books LEFT JOIN Loans ON Loans.BookID = Books.BookID
WHERE [Books].BookID NOT IN
(SELECT [Loans].BookID From Loans WHERE CheckinDate IS NULL);


John W. Vinson[MVP]
 
D

Dirk Goldgar

John Vinson said:
On Mon, 27 Dec 2004 11:37:01 -0800, "bookworm"


SELECT [Bookss].BookID, Title, Author, <other fields>
Click to expand...

In case the typo isn't obvious, I'm pretty sure that should be

SELECT [Books].BookID, Title, Author, <other fields>
 
Top