Relationships

[snowboarder234]

I set up the relationship as thus:

My goal was when I open tblMove, I would see the names of people who had checked out the movie [tblLoanTo].
However, when I open tblMovie I don't see the information from tblLoanedto:



Thanks in advance to anyone who can offer a solution.

[orange]

How about telling us in a few lines what exactly you are trying to do?
To " see " the fields and values from different tables, you will need a query.

[snowboarder234]

Thank you. My goal is to have the record from tblLoanTo appear the corresponding drop down field for each record of tblMovie when you click on the asterik. I tried the query, but because something is obviously amiss in the first two tables, the query just returns empty fields.

[snowboarder234]

I have attached the zipped file. My apology -- I should have done that in the beginning.

[orange]

I can't open accdb file.

You need 3 tables

MOVIE
movieId PK
movieName
movieCopyNumber
other movie stuff

CUSTOMER
customerId PK
customerFirstName
customerLastName
customerAddress1
customerAddress2
customerCity
customerState
customerZip
customerPhone
customerEmail


LOANTO PK
loantoId FK
movieId FK
dateBorrowed
hasbeenReturned
hasPaid



LOANTO is a junction table allows the Customer to Borrow Movie more than once. If you do not know what a junction table is, Google it.

[RayMilhon]

Opened your database you have the foreign Key in your tblLoans but there's no data in the field thus no link to the movie table. Secondly I highly recommend Orange's data structure your existing structure will result in a DB that is not in 3rd normal form and as you add more data you will have more problems.

[snowboarder234]

I can't open accdb file.

You need 3 tables

MOVIE
movieId PK
movieName
movieCopyNumber
other movie stuff

CUSTOMER
customerId PK
customerFirstName
customerLastName
customerAddress1
customerAddress2
customerCity
customerState
customerZip
customerPhone
customerEmail


LOANTO PK
loantoId FK
movieId FK
dateBorrowed
hasbeenReturned
hasPaid



LOANTO is a junction table allows the Customer to Borrow Movie more than once. If you do not know what a junction table is, Google it.

Thank you for your assistance. I took a stab at creating a junction table, it worked....sort of. I'm still confused on the relationships. I'm sorry you weren't able to open the file. Also, thanks for the exhaustive listing of fields. I had planned on completing that after the relationships are completed. In any case, I appreciate your help.

[orange]

What does sort of mean?

[snowboarder234]

Thank you for your time and assistance. I made a less than successful attempt at creating a junction table. I couldn't figure out the second junction table relationship. If you have the time and once again can open the zipped file, I would appreciate any advice you have to offer because I know, at this point, it's still not correct.

[orange]

I have acc2003. It uses mdb format. I can not use or open an accdb file.

[snowboarder234]

Opened your database you have the foreign Key in your tblLoans but there's no data in the field thus no link to the movie table. Secondly I highly recommend Orange's data structure your existing structure will result in a DB that is not in 3rd normal form and as you add more data you will have more problems.

Please see my last posting on this thread. I meant to reply quoting your response but forgot. I'm directing the response to you because you were the one able to open the zipped file. Please see latest attachment documenting my attempt at a junction table. Anyone else who can open the file is certainty welcome to respond as well.

[RayMilhon]

First You should be able to use the Save As in Access to save the DB as an MDB File.
However your table designs are all messed up. Part of it is due to the relationships you've established You need to rethink that.
Your borrowerid should have only data pertaining to the borrower
borrowerid
address
city
state
zip
phone

Your movie table should only have data pertaining to the movie
movieid
moviename
Release date
Genre

Your purchase date
and purchase quantity should be in an Inventory table. Not the movie table due to the fact you may purchase the same movie on different days in different quantities depending on demand.

Your junction table is essentially your working table
you'd have
borrowerid
movieid
mediatypeid
borroweddate
returnduedate
actualreturndate



actors maybe

[RayMilhon]

oops meant to remove the actors maybe it scrolled down out of sight and I forgot it.

[snowboarder234]

I have acc2003. It uses mdb format. I can not use or open an accdb file.

I did research on how to convert an accdb to an mdb. The instructions I found said to use the save and publish function. However, it still came up with the accdb file extension. I didn't want to waste anyone's time be uploading the same file again. If you know of another method, please feel free to share it with me.

[orange]

see this link for a model http://www.databasedev.co.uk/video_r...ata_model.html