New Table that fits with old tables

[dah]

Hi,

I have a database set up that uses a participants table and a workshop table. Each have primary keys. A third table is set up and has the two primary keys from the participant and workshop table. This was working fine.

I now need another table (Presenters) to account for people who present workshops. I set up another table along with a link table that has the presenterID (primary key) and used the workshop table I had already set up (its primary key) to link.

This seems as though it would work but now my form is not working. The workshop subform that listed all the workshops is not coming up now.

What is my next step.

dah

 

[Pat Hartman]

You don't say what the problem is. But, logically, you would not be able to maintain the presenters on your existing form. You would need a subform.

 

[dah]

I guess the problem will help.

My database has

Participant Table (ID Primary key)
Workshops Table (ID primary key)
Link Workshop to Participants (Two ID key fields)

A form with participant information on top and a subform with all the workshop information. The two individual tables have a relationship with the Link table and everything works good.

I now need possibly another table for Presenters (people who present at one or more workshops).

I thought I might make a Presenter Table (ID as primary key) and use the already made Workshops table. Make a third table Link Presenter to Workshop and make a relationship between these tables.

Then I figured I could add another subform for just the presenter information. The relationships all seem ok but now when I open the Participant Form I get a parameter box (Workshop.Presenter) and I have to enter through two boxes in order for the form to come up.

I am just experimenting on a copy of the database so nothing is completely ruined. What I need overall is a tables and forms that list all participants, the workshops they have attended and who has presented at which workshops.

Thanks,

 

[Pat Hartman]

You have two SEPARATE many-to-many relationships. You can show these on a single form with two subforms. Mainform shows workshop. Left side subform shows participants, right side subform shows presenters. Participants and presenters don't have a direct relationship. Their only relationship is through a workshop. Download my sample many-to-many db to see how to work with a many-to-many relationship. Having two separate relationships isn't a problem if your form is designed properly. Make sure that both subforms have the proper master/child links set.

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=40821&highlight=manytomany

I posted a picture of a form that I use that has a listbox for choosing which project to display and side-by-side subforms that show vendors assigned to a project and people assigned to a project. This is your situation as I understand it.

 

[dah]

I looked at the picture of the two subforms. I could not download the sample many to many database. Just tells me the database is not recognized.

Anyway, I made another table (Presenters), used the already made table (Workshops) and made a LinkWorkshopToPresenter table which has the primary keys from the other two tables. Similar to what I had with Participants and Workshops and the LinkWorkshopToParticipant table.

I made a new form with the presenter information but when I go to make the combo box for the ID (so that all the presenter information come up automatically) I get an error saying that the visual basic module was not created. As far as I can tell, I am doing the same thing as last time, so why the error.



Thanks,

 

[Pat Hartman]

Many-to-Many sample

The error sounds like one caused by corruption. I would backup the db then try to compact and repair. If the error does not disappear, then create a new db and copy all the objects from the old db.

 

[Pat Hartman]

Many-to-Many sample

I reposted the sample.