Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2006
    Posts
    213

    Unanswered: A bit confused with tables and relationships

    Hello, ive done a bit of access before but ive got myself a bit confused on my next step.

    I have a database of books that i loan out. I have a table containing the books instock. I want to be able to loan out book s and reserve books. The problem is whats the best way to do this? Should i have a form that allows me to click a button that takes the book out of the books instock list and adds it to the reserved list? The problem i see with this is when all the books had either been loaned or reserved then the books instock list would be empty and nobody could reserve anymore books which would be a vital floor. Does anyone have any ideas?

    Thanks,
    Marley.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    when all the books had either been loaned or reserved then the books instock list would be empty and nobody could reserve anymore books
    When that occurs you do, indeed, have no more books in stock! Of course, you need to also to have a command button to reverse your process; move books from being loaned or on reserve back into your instock list.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Think about it in a more "binary" fashion. You have a base inventory of books. That's it's own seperate deal.

    Now, you want to track transactions which involve making a book either available, or unavailable. I would do this by creating a "checkout" table that tells me what book waas checked out, by whom, and when it was returned. When it comes time to see if I have any books on hand, I run a query against my transaction table and see how many of a certain book are checked out, then check that against how many I have in inventory. If checked out = inventory, then we're all out, sorry...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by marleyuk
    Hello, ive done a bit of access before but ive got myself a bit confused on my next step.

    I have a database of books that i loan out. I have a table containing the books instock. I want to be able to loan out book s and reserve books. The problem is whats the best way to do this? Should i have a form that allows me to click a button that takes the book out of the books instock list and adds it to the reserved list?
    No, you don't want to be moving records between tables. That's hard work, bad idea and will end in tears. A simple way to deal with this (there are many ways as ever) is to create a column in your books table where you can tick the books that are reserved (or maybe a text box to record the person who has reserved it). You can create another column allowing you to tick whether the book is on loan or not. This simple design will allow you to create querys/reports/forms to show all books on loan, all books reserved, all books available. Of course this is very simple and you really need to investigate your full requirements before commiting to a design.

    Quote Originally Posted by marleyuk
    The problem i see with this is when all the books had either been loaned or reserved then the books instock list would be empty and nobody could reserve anymore books which would be a vital floor.
    The above design will allow you to do this of course.

    What you won't be able to do is record if more than one person has reserved a book. For this you need a seperate table called say, Reservations. This would look like this:

    ReservationID____BookID___PersonID____DateReserved

    The BookID would be a foreign key allowing you to link to the books table.

    A more complex design of managing loans is to have a loans table that records when a book is loaned from and to. This might look like:

    BookID____LoanDate______ReturnDate_____PersonID

    Note that you are not moving records here. Also note you have to start to think how you are going to identify which books are on loan and compare with your books list.

    Also, the above design doesn't track copies of the same book (they would just be treated as different books). Maybe you are happy with this.

    There are probably some "library" example databases out there somewhere and I've see many posts here on how to design a loan system.

    hth
    Chris

    ps I'm just posting this but see Teddy's written pretty much the same. I'll post anyway.

  5. #5
    Join Date
    Feb 2006
    Posts
    213
    Ah great howey. While you where posting i was working on this

    ReservationID____BookTitle___ReaderID____DateReser ved.

    Ive gone for book title instead of book id because there can be several of the same book. I just want the user who wants to reserve one to be assigned the first one back.

    As for BookID____LoanDate______ReturnDate_____ReaderID,

    I had another requirement that allows me to see when i book is over due, so i made the loans table like BookID______ReaderID________DateLoanedOut

    and i have a query that tells me how long overdue the book is. This works well now so its just a case of making this reservation bit work properly. Im really a bit stuck now on where to go.

    What im trying to achieve now is if a person wants to loan a book then the book is checked to see if its in stock, if it is then the book can be loaned to the user and a record is kept. If its not instock i want to allow the user to reserve the book for when i comes in stock.

    I need it so the database knows when the books no longer in stock. And i need the database to match users who reserve books with the first available book they have reserved.

    Is this too unclear?

    My relationships are as follows:

    tblBooks links to tblBooksOnLoan via BookID
    tblBooks links to tblBooksReserved via BookTitle

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •