Many-to-Many Relationship -

[PSSMargaret]

I could use some guidance. I have set-up simple many-to-many relationships before but I am in need of a more complex one OR maybe it’s not really a many-to-many relationship but I want to make sure I set-up it correctly from the beginning.

I have tblContacts and frmContacts and for each Contact I need to add a subform where I can enter the populations that apply to them. Each Contact can have multiple Population Levels with corresponding Population Types and Population Subtypes.

Contact 1 and Contact 2 may have totally different values than Contact 1.

Contact	Population Level	Population Types	Population Subtypes
Contact 1	Federal	Business Association	HOA
Contact 1	State	Disabled	Community Partner

Contact	Population Level	Population Types	Population Subtypes
Contact 2	Local	Tribal	Private Business
Contact 2	Federal	Youth	Advisory Council

I did set-up a test where the junction table includes ContactID and PopulationLevelID and they are both primary keys in the junction table. ContactID is automatically unique and it won’t let me changes it. PopulationLevelID is not unique.

I also added to the junction table fields for Population Types and Population Subtypes, however, if I add another row to the table for Contact 1 where there is a second Population Level of Federal, I get and error stating.

“The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index or redefine the index to permit duplicate entries and try again. “

I changed both ContactID and PopulationLevelID indexes to Duplicates OK, but I still get the error. I’m doing something wrong or maybe it’s not really a many-to-many relationship?

If it a many-to-many relationship, can you provide some guidance on how to set it up correctly? Any help is greatly appreciated.

[Gicu]

Do you mind uploading a small sample file with your tables (no sensitive data, just a couple dummy records to show the issue)?

Cheers,
Vlad

[PSSMargaret]

Actually, I think I resolved it. I made all four fields be a Primary Key and it's working. Let me if this is wrong or dangerous in any way. I don't have the time to strip down the database to send to you. I'm working on a deliverable.

[orange]

Margaret,

Please
-tell us about the application in simple English (an overview of the "business" involved,
-show us any tables and relationships you have,
-some sample data - a few more records so we can see what and how these subjects fit together.

Contact

Population Level

Population Types

Population Subtypes

To test your set up you need some sample scenarios and sample data.

Good luck.