Rental Database help

[Caity]

Hi, i'm making a video game rental system for my A2 coursework and so far have made 4 tables, created a forum (and sub-forum) from a query pulling the relative sections from each table.

My tables are:

tblGames
PK: GameID

tblGamesCopy
PK:GCID(game copy ID)
FK: GameID

tblRental
PK: RentalID
FK: GCID

tblCust
PK: CustID
FK: RentalID.

The way my relationships are, a customer can make many rentals, a game can have many copies, a copy can be rented many times. (Say if I need to make further elaboration on the basics of this).

Now, my problems:
I created a combo box that included a Game Name, Price, Genre etc. Using VBA (e.g. Me.GameName = Me.ComboBox43.Column(0)) I tried to make it so the boxes would Auto-populate on the selection, however it won't work. I tried searching for a solution and all I got was if I changed it to "Limit to List" it'll work, but it still doesn't (when I make a selection, NOTHING auto-populates)

Secondly, I was wondering, how would I actually make it so a copy is taken? I have my form set where the employee would enter the name of the game, but don't know how to link it in the amount or available copies that can be rented.

I can't really move on with my coursework until I've got this finished :banghead:... Any help is really appreciated.

 

[Steve C]

Combo Boxes don't belong in Tables - even though Access lets you do it! And tells you that you can too!!

I didn't beleive the good people on this forum when they told me that! - I am a bit wiser now.

 

[plog]

It looks like you have the relationships correct, but based on what you are trying to do I'm guessing your tables aren't properly structured. I smell redundant data.

Could you post all the fields of the tables?

 

[Caity]

Combo Boxes don't belong in Tables - even though Access lets you do it! And tells you that you can too!!

I didn't beleive the good people on this forum when they told me that! - I am a bit wiser now.

I don't have an actual combo box in the table, I created a query that pooled the Game Name, along with the price and stuff from multiple tables and then made the combo box in a form with the query showing the values (I hope that made sense, I don't think I worded it well >.<)

 

[Caity]

It looks like you have the relationships correct, but based on what you are trying to do I'm guessing your tables aren't properly structured. I smell redundant data.

Could you post all the fields of the tables?

I put the primary keys in red and foreign keys in orange
tblGames
GameID
GameName
Developer
Director
Genre
Rating (Age)

tblGamesCopies
GCID
GameID
Console
Active (boolean, to check if it's currently checked in or out)
UnitPrice (I put this in the Copy table and not the Game table because I was hoping to charge a different price per console)

tblRental
RentalID
GCID
CustID
DateHired
DateDue
DateReturned
OverdueCharge

tblCust
CustID
Title
Forename
Surname
DOB
Age (invisible in form, it's included to store the persons age for calculations of whether they are the appropriate age for a rating)
Address
PhoneNumber