Another Library Database

[serak]

Hello,
I'm trying to set up a database for a library but I'm having a lot of difficulties and I'd be very greatful of any help. Well my main problem is the loaning and returning of books (which is a pretty big problem considering...). Anyway I can't figure out a way of having multiple orders (a maximum of 3 books at a time) and then being able to update tables for when the books loaned out and returned. Then another problem is how fines would work, ie [('returndate'-'datedue')*whatever] but how would this actually be displayed and used. I apologise for not really explaining myself well, but here is what my tables and relationships look like (which I think maybe where my problems are starting):

http://homepage.ntlworld.com/k.mcareavey/Database/relationships.bmp

and the actual database:

http://homepage.ntlworld.com/k.mcareavey/Database/Monro Library Version 2.zip

One last thing - I had a look at some of sample library databases but I the only one that did really what I wanted used to much VBA and I couldn't really figure out how it worked. Thanks a lot for any help.

 

[snoko]

Serak,

Have you only got one copy of each book? If not you'll need to create another table for this (tblBookCopy) and link that to the loan table. It's an individual copy of a book that get's loaned not the book title.

Once you get this right it should be a bit easier.

 

[serak]

Thanks for the reply, but I don't completely understand.

Do you mean that tblBook should be linked to tblBookCopy which should then be linked to tblLoan?

And should tblBookCopy just have like the quantity of books or something? (I had a quantity field it tblBook before but I deleted it for some reason)

I've been working more on it and I removed the Overdue field for tblLoan - it wasn't needed. I have also got update queries for DaysOverdue and FineAmount that work.

Thanks again for the reply.

 

[snoko]

What happens if you've got two copies of the same book. They'll both have the same ISBN number, therefore which one do you loan out ? You have to make each copy of each book unique.

tblBookCopy

intID - autonumber PK
ISBN - FK to tblBook
Copy - number

tblLoan

intID - autonumber(PK)
intBookCopy - number (FK to tblBookCopy)

 

[serak]

Ahhh got you - thanks.