Question on Relationships In MS Access

[thinboywide]

Hi i need help with a relationship within access, i need to connect the two following together:

Loan (LoanMemNum, CopyNum, Ldate)
Video (Vcode, VTitle, Date Made, Director, Genre)

and i need the relationship from loan to video to be a one to many

Thanks

 

[neileg]

You need to hold the video ID (Vcode?) in the Loan table.

 

[thinboywide]

Hi

ive done what you said but when i go to connect it access puts it the other way round.

It does Many to one from Loans to Video. But i need it to be one loan to many video's

Thanks

 

[Dennisk]

in the relationship window right click the relationship and change the type of join

 

[thinboywide]

Hi,

Thanks i think that has fixed the problem, Will doing this effect the database at all?

 

[thinboywide]

Hi,

I tried your solution but i still have the problem. Can anyone help?

 

[boblarson]

It does Many to one from Loans to Video. But i need it to be one loan to many video's

So, let's think about this for a second. Can a Loan can have many videos on it? I would think so. Can a video be loaned out more than once? If so, then you are incorrect in your thinking. You don't need a one-many relationship with this, but rather a many-to-many. So, you would need a junction table.

 

[thinboywide]

Hi i still need help but i need the relationship to be one to many, is this possible? will it cause any problems within my database when it is implemented?

Thanks for all your help

 

[thinboywide]

Hi,

In all honesty is it best that from Loan to Video be a n:1 ?

Also would this work overall and would it cause any problems when implementing my database?

Thanks Alot

 

[boblarson]

You need to hold the video ID (Vcode?) in the Loan table.

Actually for a one Loan to Many Video you will need a LoanID in the Video table to store it. Now, just so you know, I've already told you that you really have a many-to-many situation but since you insist otherwise we'll let you have a go at it and then you'll figure it out after much pain and suffering.

If you truly have a one-many situation (1 Loan / Many Videos), then each video will ONLY be able to be associated with one loan. Once you've assigned the video it will not be able to be associated with another loan unless you delete the existing record.

With a many to many set up you would have (I'm assuming that Vcode is the autonumber primary key):

Loan table:
LoanID (autonumber - Primary Key)
LoanMemNum
CopyNum
Ldate

Video table:
Vcode (autonumber - Primary Key)
VTitle
Date Made
Director
Genre

LoanedVideos table:
LoanedVideoID (autonumber - Primary Key)
LoanID (Long Integer - Foreign Key)
Vcode (Long Integer - Foreign Key)

Hopefully that will get you on the right path.

 

[neileg]

I agree 100% with Bob. I would add that Access does not determine the relationship, it's the real world that does that. Access is only modelling what exists in fact.

You model a one to many by holding the unique identifier (the primary key) of the 'one' side in the related records of the 'many'. That's how Access knows what the realtionship is - there's no hidden routine that let's the program think "Ah, he's doing a video loan application, therefore this must be this kind of relationship".

 

[thinboywide]

Hi,

Im affraid i still need help, as a matter of opinion, which one do you will work

Member 1:n Loan n:1 Copy n:1 Video

also there is a link from

Member 1:n Reservation n:1 Video

Or

Member 1:n Loan1:n Video 1:n Copy n: 1 Video

also there is a link from

Member 1:n Reservation n:1 Video


The entities are as follows:

Video (Vcode, VTitle, Date Made, Director, Genre)
Copy (CopyNum, Vcode, Due Date)
Loan (LoanMemNum, CopyNum, Ldate, Video Code, In Out)
Member (MemNum, MName, MAddress)
Reservation (VCode, resMemNum, Resdate, CollectDate

Any help is much appericiated as i am new to access

Thanks

 

[haze]

tutorial

Hi there,

I'm also new at access but I've been reading a lot of tutorials and I when I read your post I remembered this one.

http://www.profsr.com/access/accless0.htm

It's a really great step-by-step tutorial on how to build a video rental database which sounds just like what you're trying to do. You can also download the sample database that goes with it and look at the table structures and forms to see how they work. Hope it gives you some help!

 

[thinboywide]

Function and Non Functional Requirements

Hi,

Thanks for that tutorial, i need a little help on functional and non functional requirements, any help would be much appreciated.