Relationships

[Bez]

Hi Guys,

Im in year 10 at high school and our group is completing our 1st piece of coursework. It is a design project, And my friend would like to set up a relationship.

He has 2 tables and i would like to link

Video Number (Table 1)

With

Member Number (Table 2)

So he can view what Video each Member has rented out.

Would this be possible?

Regards

Craig

 

[Robert Dunstan]

Hi there

Without knowing too much about the structure of your tables, it can be done quite easily but I need to know a bit more information about your project. Will your project allow for more than one copy of the same video? Can a member rent more than one video at the same time?

 

[Bez]

Hi Rob,

He would like his project to have more than one copy of the same video.

He would also want to allow more than one video to be rented at the same time by a member.

Regards

Craig

 

[Bez]

Also,

In MY project, I have two tables...

I have a "Staff" table and a "Massage" table.

How would i be able to link the two together.

Where the Massuers name from the "Staff" table would link with the Massuers name in the "Massage" table.

Then i will be able to view which Massuer is giving a massage at a certain time.

Regards

Craig :d

 

[Robert Dunstan]

Hi Craig,

Ok I'm looking at the first question for you now.

With regards to your last post, assuming in your 'Staff' table you have a primary key field e.g. staffID to uniquely identify that member of staff, then in your 'Massage' table you need to add another field called 'staffID'. Set its datatype to a number field and this will become your foreign key.

In the 'Relationships' window drag the staffID field in the 'Staff' table to staffID in the 'Massage' table and Access will create the relationship. This should create a One-to-Many relationship i.e. one member of staff can perform many massages.

HTH
Rob

 

[Robert Dunstan]

Hi Craig


Ok have a look at the relationships in the attached example which is in Access 2K.

I've created 4 tables:

tblVideo
tblVideoDetails (Junction Table)
tblRentals
tblMembers

tblVideo will contain the details of all the videos available, tblRentals contains all the rental history for each video as applicable, tblMembers is your membership list, as for tblVideoDetails this will act as a junction table between tblVideo and tblRentals as one video can have many rentals (because you have multiple copies of the same video) and one rental can have many videos.

This is a simple example so you can get an idea as to what relationships you need. Somebody here may come up with a better solution but hopefully this will give you some idea.

Post again if you need more help.

Rob

 

[Bez]

Hi Rob,

Thanks for that...It helped him alot.

But...I can't get my head around my project..

The staff Id etc for the massage table.

Can you help with it?

Regards

Craig

 

[Robert Dunstan]

Hi Craig,

Can you post an example here so I can better understand your table structure and relationships?

 

[Bez]

Hi Rob,

Im at school at the moment and i dont have the file here.

However, I will try to explain..

I have 3 tables now

Tbl_Members

Tbl_Staff - Contains a primary key field with auto number to give the Staff an ID

Tbl_Massage - Contains a field called Staff ID which i would like to link.

What i would like to do is link to Staff and Massage table so i can select which member of staff is giving a massage at a certain time.

Also, Would it be possible to link the members table to this so i can view which Massuer is giving a massage to which member.

Regards

Craig

 

[Robert Dunstan]

Hi Craig,

Sorry for taking so long to come back to you.

OK, tbl_Massage needs to act as a junction table between tbl_Staff and tbl_Members. If you think about the relationships logically you have the following:

1 staff can give many massages.
1 member can have many massages
but onyl 1 staff can give 1 massage to 1 member at a time. i.e. a one-to-one relationship.

In order to create this you need a junction table which contains only the foreign keys to tbl_Staff and tbl_Members. This is where tbl_Massage comes in.

Have a look at the attached example. Hope that makes sense

Rob

 

[Bez]

Thanks for that Rob,

It helped me a little more...

However, Instead of the relationship displaying the Staff ID ... Would it be possible to display the Staffs Name, The Time and Date of the Massage, And whether or not the Member has paid?

I would also want this to be set up into a form, So i can use it to "Book a Massage"

Regards

Craig

P.s
Do you have any Chat programs? That way we can speak quicker..

 

[Robert Dunstan]

Hi Craig,

Here's a revised example.

Basically tbl_Massage will become your main table. The reason why StaffID & MemberID are displayed is that these are the link fields. What you would need to do is create a query like I've done to display all the relevant information. Then you create a form and base it on the query.

 

[Bez]

Hi Rob,

Thank you very much for this

Im going to have to work on it now...

I sent you a PM....Can you check it please?

Regards

Craig

 

[Bez]

Also Rob,

Would it be possible for you to explain how this was done?

As i need to explain what happens as i go along.

Regards

Craig

 

[Bez]

And,

How do i delete a Relationship once i have created one?

Regards

Craig

[EDIT] I have done it...

 

[Robert Dunstan]

Hi Craig,

Wow all these questions

I did get your PM...thanks, unfortunately I'm not able to download any chat software as I only have internet access here at work, this is the best and only forum I use

I'm leaving shortly so I'll respond to this tomorrow

Rob

 

[Bez]

Ok Rob,

When i drag my primary key field in the relationship view to the field i want to link it to (In another table) i just get a line linking them.

I dont get a number "1" or one of those signs....

Why is this?

Regards

Craig

 

[Robert Dunstan]

Hi Craig,

Ok to answer your posts:

How do i delete a Relationship once i have created one?

Go to the relationship window, right click on the relationship line and select 'Delete'.

When i drag my primary key field in the relationship view to the field i want to link it to (In another table) i just get a line linking them.

Again go to the relationship window and right click on the relationship line. Select 'Edit the Relationship' and this displays the dialogue box on screen. Put a tick in 'Enforce Referential Integrity', this will basically ensure that for every record on the many side there must be a related record on the one side. I suggest you search this forum and look up referential integrity and relationships in Access help as there's a wealth of info there.

HTH
Rob