Many to many relationships

[Tazzy via AccessMonster.com]

Hi all,

I'm in the process of setting up a database for a project that involves
actors, shows and venues and have set up tables as follows;

TblActors - Stage name(Primary key), real name, etc.

TblShow - Show name(Primary Key), venue, dates, etc.

TblVenue - Venue(Primary Key), address, phone number, etc.

TblJoin - Two fields, primary keys from Actors and Shows

I think I have done this correctly to get a many to many relationship, as one
actor can appear in many shows and one show can have many actors. There are
the usual one to many links from TblActor and TblShow to TblJoin.

My problem is that when I then set up a form for the actor, and try to have a
subform for shows, I don't seem to be able to link the forms successfully.
On checking the tables and clicking on the + by the side of the record I know
that it should show the details of all shows the actor appears in, but they
are just blank. I think I may have missed something fundamental in setting
up the tables. I've tried including a field for actors in the Show table and
vice versa, but it doesn't make any difference.

TblJoin never shows any records entered

Any help gratefully received (as ever!)

Tazzy

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200707/1

[Jason Lepack]

Make sure that you've created your relationships for the tables (Tools-

>Show Relationships) and that you have enforced referential integrity.

Create a form based on Actors. Create a subform on that form based on
Join, make sure you use continuous forms for the subform. Hide Stage
Name on the subform. Change Show Name to a Combo Box and set it's row
source to "SELECT [Show name] FROM TblShow"

That's it. You're design is great so far. Any questions, just post
back.

Cheers,
Jason Lepack
On Jul 6, 4:54 am, "Tazzy via AccessMonster.com" <u26845@uwewrote:

Hi all,

I'm in the process of setting up a database for a project that involves
actors, shows and venues and have set up tables as follows;

TblActors - Stage name(Primary key), real name, etc.

TblShow - Show name(Primary Key), venue, dates, etc.

TblVenue - Venue(Primary Key), address, phone number, etc.

TblJoin - Two fields, primary keys from Actors and Shows

I think I have done this correctly to get a many to many relationship, as one
actor can appear in many shows and one show can have many actors. There are
the usual one to many links from TblActor and TblShow to TblJoin.

My problem is that when I then set up a form for the actor, and try to have a
subform for shows, I don't seem to be able to link the forms successfully.
On checking the tables and clicking on the + by the side of the record I know
that it should show the details of all shows the actor appears in, but they
are just blank. I think I may have missed something fundamental in setting
up the tables. I've tried including a field for actors in the Show table and
vice versa, but it doesn't make any difference.

TblJoin never shows any records entered

Any help gratefully received (as ever!)

Tazzy

--
Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/2007...

[Tazzy via AccessMonster.com]

Hi Jason,

Thanks for that, it's made me feel much better and stopped a lot of head
scratching. Will this then ensure that I can gain access on this subform to
all of the data on the Show table, as I will need to have all of this
information available as well. I take it from what you have said so far that
nothing actually appears in the Join table, it's just a method of making the
many to many relationship.

Thanks so far,

Tazzy

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200707/1

[Tom van Stiphout]

On Fri, 06 Jul 2007 14:02:35 GMT, "Tazzy via AccessMonster.com"
<u26845@uwewrote:

No, a join table contains the values of the primary keys of the two
parent tables.

-Tom.

>Hi Jason,

Thanks for that, it's made me feel much better and stopped a lot of head
scratching. Will this then ensure that I can gain access on this subform to
all of the data on the Show table, as I will need to have all of this
information available as well. I take it from what you have said so far that
nothing actually appears in the Join table, it's just a method of making the
many to many relationship.

Thanks so far,

Tazzy

[Tazzy via AccessMonster.com]

I've created the forms as you suggested, (tried the second one before getting
your message) and yes, they work fine. The only problem I see is that the
subform will only contain info from the join table ie 2 fields. I would need
to show more detail than that, such as the venue, dates etc which are all
contained on TblShow. How can I get these to appear on the subform as well,
the field list only shows the fields from TblJoin. By the way, couldn't
figure out how to change the Show field to a combo box, so I added one, then
hid the original. Presumably there's a more professional method

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200707/1

[Jason Lepack]

If you want the venue information that you can create dlookup fields
to collect it, or put a subform on the subform that shows the
information from the show table.

The key thing with forms is to make it so that the data that you are
using is updatable. If you don't want it to be updatable you can put
whatever data you can pull together from your database on it.

Cheers,
Jason Lepack
On Jul 6, 11:42 am, "Tazzy via AccessMonster.com" <u26845@uwewrote:

I've created the forms as you suggested, (tried the second one before getting
your message) and yes, they work fine. The only problem I see is that the
subform will only contain info from the join table ie 2 fields. I would need
to show more detail than that, such as the venue, dates etc which are all
contained on TblShow. How can I get these to appear on the subform as well,
the field list only shows the fields from TblJoin. By the way, couldn't
figure out how to change the Show field to a combo box, so I added one, then
hid the original. Presumably there's a more professional method

--
Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/2007...

[Tazzy via AccessMonster.com]

Thanks very much for all your help and advice, much appreciated. I managed
to obtain what I needed by using a command button linked to the stagename
that then opened the Actors form to show all details of that actor, but will
try and figure dlookups on a backup of this one.

Ta ever so
Tazzy

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200707/1