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:
Hire_length (either daily or weekly)
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)
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:
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?
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.