multiple Many to Many joins - Access 97

  • Thread starter Thread starter John Desselle
  • Start date Start date
J

John Desselle

I have searched the news groups and found some helpful stuff on Many to Many
joins. It is talked about all over the place, but I didn't find anything on
multiple Many to Many relationships.

Using Access97

We are about to start writing a book and sent a small form to our members
asking if they would like to help in the writing. I am getting answers and
need to capture the results. Here is what I have set up:

qryBookAuthors (this is needed because I need only a small numbers of
members from our main member database and want the ability to pull contact
info if needed and do not want to keep the contact info in both databases
current.)
- Full_Name
- Last_First (for sorting purposes

tblResponse
- ResponseID (AutoNumber - Primary Key)
- Full_Name (Look Up field from qryBookAuthors
- Answer (Yes/No)

tblTopics
- TopicsID (AutoNumber-PK)
- Topics (I have typed in the different topics from the list we sent out
for the members to choose from)

tblPotentialAuthors
- PotentialAuthorsID (AutoNumber-PK)
- PotentialAuthors (Text - 255 limit)

tblJoins
- JoinID
- ResponseID
- TopicsID
- PotentialAuthorsID

Many to Many part:
One Responder may have chosen many topics
One Topic may have been chosen by many responders
One Response may have chosen many PotentialAuthors

Relations:
- tblResponse<>tblJoin<>tblTopics
(ResponseID-PK<>ResponseID----TopicsID<>TopicsID-PK)
- tblResponse<>tblPotentialAuthors
(PotentialAuthorsID<>PotentialAuthorsID-PK)

I have a main form based off of the tblResponse with a cmbFull_Name limited
to list
Yes/No check box for if they want to help or not
sfrmTopic which only has a cmbTopic. sub form based off tblJoins and list
box source is based off tblTopic

I can open the main form, choose which Author, check Yes or No, select one
or more Topics, but I can not figure out how to TYPE in one or more or none
Potential Authors.

Any help is greatly appreciated.
 
I figured out what I did wrong. I had the one-many relation backward
between the Potential Authors and Responses.

Guess it helped to type it all out like this. I hate admitting I skipped
step one in database building....WRITE IT OUT FIRST!!!
 
Back
Top