After many weeks of reading, writing, downloading solution after solution I cannot get this database to work and prevent duplicates.

Here is the situation:
I am building a community database for our local advice centre. I have a table of services and agencies who provide services. These might be doctors, dentists, financial advice centres child care etc. I can have many agencies providing the same service (i.e. doctors) or I can have a single agency providing a range of services - not just one.

So, i have set up a services table, an agency table and a junction table to catch the foreign keys of each of the two primary tables.

I have then set up a form to access all the fields on the two primary tables and have defined the relationships between the primary keys and the junction table in the relationships section of access.

When I run the form I can update each table and the junction table is updated with the foreign keys to match the primary keys of each record but I cannot stop duplicates from occurring!

If I try to link an agency to a service that is already in the services table, a new record is created as if the service did not exist and a foreign key is assigned to it in the junction table and the service name is entered in the services table as if it were a new service.

Is there anyone who can explain what I should do?

---

Reply from Richard Rost:

I would recommend reviewing my lessons on Junction Tables and Many-to-Many Relationships first. I would build a form showing the Service as the parent record, and then select Agencies as the subform records; and then another form for the reverse relationship. The forms will automatically handle the relationships for you as long as you have it set up properly. It won't automatically prevent duplicates - you will still be able to enter the same service for the same agency twice - but you can prevent that by using a Composite Key in your junction table.

If you would like one of my Techs to take a look at your database file and figure out where you went wrong, then you can submit it via my TechHelp service.