Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2009
    Posts
    1

    Smile Unanswered: Game rental system database design issues

    I am using Microsoft Access (I've been forced to use it). I need to design, from scratch, a simple game rental store system.
    I am uncertain of how to do some things. Here's what I've done so far:


    Table: Customers
    ----------------

    ID_Number*
    Name
    Address
    Suburb
    Contact_number
    Gender


    Table: Games
    -----------

    Barcode*
    Title
    Genre
    Price
    Available (boolean)
    Hire_length (either daily or weekly)

    Table: Rentals
    -------------

    Rental_ID*
    ID_Number (this is the ID number of the customer who is borrowing the game)
    Name (this is the name of the customer who is borrowing the game)
    Barcode (this is the barcode of the game being borrowed)
    Title (this is the title of the game being borrowed)
    Hire_length (this is the hire length of the game being borrowed)
    Price (this is the price of the game being borrowed)
    Date

    *Primary key

    With said tables, I would need to be able to:

    - Insert new customers
    - Insert new games
    - Insert new rental records


    The forms for the first two tables are simple enough.

    However, for the third form (to insert new rental records) the form would be like this:

    -------------------------------------------------

    Rental ID: _____ (automatically-generated number)
    ID Number: _____________
    Name: _____________
    Barcode: ______________
    Title: _____________
    Hire length: ___________
    Price: _______
    Date: ____________ (automatically-generated date)

    --------------------------------------------------

    In the above form, when I enter the customer's ID number, how could I automatically make Access get the customer's name matching the ID and
    populate the 'Name' field of the form? Further more, when I enter the game's barcode number, how can I automatically populate the the
    'Title', 'Price' and 'Hire length' fields matching that game's barcode number? It would be great if automated this way, as opposed to manually
    entering the game and customer details.


    When retrieving data from the database, there need to be reports for the following:

    1. Display a specific customer (assume it's by entering the customer's ID) and any games he/she has borrowed.
    2. Display a list of games that are currently available and sort them by their hire lengths (e.g. weekly games grouped together).
    3. Display details of a certain game (assume it's by entering the game's barcode number).
    4. Display a list of customers who currently have games rented out.


    Reports 2, 3 and 4 are simple enough to make. However, report 1 doesn't make sense to me.


    For report 1, I would need two queries:

    - Get the user whose ID_Number matches the input number from the 'Customers' table.
    - Get the details of all the records from the 'Rentals' table where the row's ID_Number is the same number used in the first query.

    Both of these queries would need to share the same report. How could I possibly do this AND only have to enter one ID number which is used for both queries?

    If you can solve my problem, it would be great.

    Thanks in advance.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    In table Rentals, you DON'T need to store any customer or game information beyond the primary key. Any time that you need to see some combination of them, you can use a query to join them together.

    That said, there's nothing wrong with displaying the customer and game information in the form to create a rental record. The easiest way is probably to have combo boxes for the customer ID and barcode, and code their OnChange events to push the relevant details into the other controls on the form.

    For your first report, your query needs to contain all three tables. Assuming that you've set the relationships up, they will all join appropriately in the query window, and then you just need to select the fields from the Customers and Games tables that you want to see in the report. When you come to build the report, the wizard will recognise the logical grouping of the fields by source table.

Posting Permissions

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