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
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    So, what have you got so far?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  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,424
    Provided Answers: 8
    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

    See clear as mud


    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 based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  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
  •