Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Posts
    2

    Unanswered: Help with creating 'complicated' query

    As a college project i have to create a database for a company which loans things out, i decided to do a game rental shop. I decided to do some work on it over the half term and have come to a seemingly complicated query idea.

    I have three tables: customers, games and rentals. The customers and games tables have one-to-many relationships with the rentals table (a customer can rent things many times, a game can be rented many times).

    Now i want to create a query which will find any games where the Status field is set to "Damaged" and bring up the customer number or possibly customer details of the customer who last rented the game. I am unsure of how to do the second part of this.

    A rough idea of my tables..
    [Customers]
    Name, address etc, customer number (primary key & linked to rentals table)

    [Games]
    Name, platform, item number (primary key & linked to rentals table)

    [Rentals]
    Date, expected return date, return date, status (in stock, damaged, etc), rental number (primary key), customer number (who was it that did this rental), item number (what was it that was rented)

    I'm quite lost as to how to create this query, i have created a similar query though which searched the rentals table to find all rentals which were late and the customer details of the person who has the game but this worked easily because the rentals table has a customer number and item number field which are linked to the other tables via one-to-many relationships.

    Can anyone give me some ideas on how i could create this query? After writing this i have ideas popping up in my head but they don't seem to be enough.

    I'm not asking you to do my homework for me, simply for help on creating this query. If i get no replies by tonight then i'll ask my tutor at college about it tomorrow.

    Thanks

  2. #2
    Join Date
    Sep 2004
    Location
    Raleigh, NC
    Posts
    146
    I might have misunderstood what you're attempting to do, but this doesn't seem complicated in the least. Just create a Select Query that contains the "Status" field from your "Rental" table and use "Damaged" as the criteria. Tie the query to an event (like a command button OnClick), return the results on your form, and everything should be peachy.

    On a side note, you might want to include that very same "Status" field in your "Game" table rather than your "Rental" table. Reason being, the status is a property of the game itself (in, out, damaged, etc) and should therefore be stored in your "Game" table. It would be redundant to store the value each time you rent the game - best to store it once and update it as needed. Just a thought. HTH and good luck.
    Last edited by smacdonaldnc; 10-31-04 at 10:19.

  3. #3
    Join Date
    Oct 2004
    Posts
    2

    Re:

    Ah sorry i got mixed up when writing the post, the status field is in the games table not the rentals table.

    So with this the answer you gave would no longer work. If i did the query you said on the games table it would simply come up with all games which are damaged and i would like my query to be able to come up with the customer number or customer details of the customer who last rented the game.

    The ideas i've had so far are.. Add a new field to the game table which holds the customer number of the last person to rent the game, then would i create a relationship between the customer and game tables to allow access to the customer details? I've just completely confused myself with that but hopefully it makes sense, any ideas?

  4. #4
    Join Date
    Sep 2004
    Location
    Raleigh, NC
    Posts
    146
    Quote Originally Posted by InsaneBoarder
    Ah sorry i got mixed up when writing the post, the status field is in the games table not the rentals table.

    So with this the answer you gave would no longer work. If i did the query you said on the games table it would simply come up with all games which are damaged and i would like my query to be able to come up with the customer number or customer details of the customer who last rented the game.

    The ideas i've had so far are.. Add a new field to the game table which holds the customer number of the last person to rent the game, then would i create a relationship between the customer and game tables to allow access to the customer details? I've just completely confused myself with that but hopefully it makes sense, any ideas?
    Use the following structure and create One-to-Many Relationships between the highlighted fields:

    [Customers]
    CustomerID
    FirstName
    LastName
    Address

    [Games]
    GameID
    GameName
    Platforum
    GameStatus

    [Rentals]
    RentalID
    CustomerID
    GameID

    RentDate
    DueDate
    ReturnDate

    Then create a form and bind text boxes to each of the fields to be displayed. Then create your query and use "Damaged" as your criteria. Use a command button to execute the query and you're off to the races.

Posting Permissions

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