Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2013
    Posts
    5

    Question Unanswered: Multiple Hires - Video Store DB

    I am struggling to get my head around allowing multiple hires in my rental database I am doing for school.

    note: all copies of media have individual LibraryID even multiple copies

    I have a transaction table and a hire table as follows;

    :Transaction:
    TransactionID PK
    DateHired
    DateDue
    DateReturned
    CustomerID FK
    HireID FK

    :Hire:
    HireID PK
    LibraryID FK
    TransactionID FK

    :Library: and :Customer:
    <<this is straight forward>>

  2. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,105
    Provided Answers: 18
    So, what have you got so far?
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  3. #3
    Join Date
    Jul 2013
    Posts
    5

    Please see attached

    Quote Originally Posted by weejas View Post
    So, what have you got so far?
    So far this is what I have, I had to remove relationship joiners as they were adding foreign keys I believed I wouldn't need.

    I need to enable multiple hires from one person.
    Attached Files Attached Files

  4. #4
    Join Date
    Nov 2011
    Posts
    413
    So If every Transaction can have multiple hires, link your TransactionID from table Transaction to table Hire.

  5. #5
    Join Date
    Jul 2013
    Posts
    5
    Thankyou for help. I know have another issue. I have 3 tables off my Library table. Media, Genre and Console each have a (Text) and (Key) to save repletion of data. My problem is I am creating a form for Library Data Entry and I want to have a combo-box on form for each of the three tables. I have been able to get the ID fields in there but as they are just numbers it is a little difficult to know what they refer to for end user. I have been able to in the past used a syntax that allowed me to select certain columns from a table for values. But cant work it out again. Any Ideas?

  6. #6
    Join Date
    Nov 2011
    Posts
    413
    I don't know enough about your db to help much at this point.Why not either a qry and combine all of the tables for your form or else use subforms and link to ID ? Of course you can use a combo as a lookup if you wish, but I need more details to offer any real suggestions.

  7. #7
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,482
    Provided Answers: 11
    I can see one little problem

    you library table only have 1 Media 1 Genre 1 Console per Library

    i would put a other table

    between the Library and the Genre so you can have many Genre to 1 Library same on duping records
    same with the Media and Console ones

    off the top of head

    you could put the number of Copies u have of each title in the Library

    then by count the number of transaction for a LidraryID on a date where the datereturn is null would give number - copies would give u the number u have left

    INcome

    I think u would need

    INcome
    INcomeID PK
    CustomerID
    TransactionID
    PaidValue
    DatePaid

    in the Library table add the PriceValue

    Why store pricevalue, Paidvalue twice as its the same number : good point

    Because you can put up the price on a item and it wouldn't change the Income Value that you already have got.

    Form the the Income table
    u can get the Best customer
    u can get the best Library rental by join to the Transaction table.
    and so on

    I have just PM you a web address
    Last edited by myle; 07-23-13 at 02:19. Reason: Spelling
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  8. #8
    Join Date
    Jul 2013
    Posts
    5

    Library Categories

    All my individual assets (Movies or Game) have an individual library ID anyway, so I have no need for the copies scenario.
    I have attached my ER Diagram.
    If I have to Im sure I can create a query for each of the Library Categories, but was hoping for a more professional way.

    Another one, is there any easy way to customise AutoNumber to GH##########.. Where # = number
    I tried with "InputMask" but it seemed to only allow data after the 4th #
    Attached Thumbnails Attached Thumbnails GreatestHits_Relationships.jpg  

  9. #9
    Join Date
    Jul 2013
    Posts
    5

    Transaction Form (POS)

    Here is My simple Transaction Form, it functions for now. Although will require some cleaning up.
    Attached Thumbnails Attached Thumbnails SimpleTransaction.jpg  

Tags for this Thread

Posting Permissions

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