How do I get Access to recognize duplicates in data entries? (many-to-many relationships)

Question

tl;dr: How do I make sure that Access doesn't create two rows in the Keywords table for this entry and instead recognizes that it's the same and assigns it the same KeywordID?

More info: I have created a database that will hold information about results from an academic literature search. There are three main "entry ways" into the database - through the "resource" (journal article), the "project" (the project described in the journal article), and the "city" (the city where the project described in the article took place).

For data entry, I've created a resource form (with subforms for Author, Keywords, and Project), a project form (with subforms for City, Tools, Services, and Disservices), and a city form (no subforms). Resources can describe multiple projects, and projects can be implemented in multiple cities-- in the same way, a city can have multiple projects, and a project might be described in multiple resources.

The subforms are based on queries that bring together both IDs from the junction table and "name" fields from the variable tables in question. Everything seemed to be working fine in data entry until I came to a resource that had the same keywords as another resource. When I looked to see how Access handled these (whether Access had recognized them as the same keyword in multiple resources), I realized that it did not. Instead, there are two rows with the same keyword in the Keywords table, but different KeywordIDs.

Since I created all the subforms in the same manner, I assume that if I come across the same Author, I'll have the same issue. Currently the subforms are in datasheet view and they're empty when you create a new project so you have to type the values in.

Thank you! I just started using Access a week ago, so I'm very new and would appreciate any help and guidance! I don't have any experience with VBA, so if there is a way to do this without code, I'd be very appreciative (I have experience with other languages, so if there isn't a way without code, I could try to take it on-- but I'm a little bit on a time crunch, so the less additional knowledge needed... :-p ).

Notes on what I've tried so far: The keyword in question is "urban resilience" and the two KeywordIDs are 5, 12. I took the urban resilience with the keywordID 5 and manually added the second resourceID as related (4). Now if you look at the record for ResourceID 4, you see urban resilience twice.

Answer 1

I must admit that I didn't read all of your question. Its too long to read.

If you have a many-to-many relationship, you have a link table. You can create a compound primary key for this table consisting the 2 foreign keys. This prevents duplicates:

   T1
  ┌────────────┐                                T2
  │ ID (PK)    │◄──┐                           ┌────────────┐
  ├────────────┤   │  ┌───────────────┐     ┌─►│ ID (PK)    │
  │            │   └──┤ T1_ID (PK, FK)│     │  ├────────────┤
  └────────────┘      │ T2_ID (PK, FK)├─────┘  │            │
                      ├───────────────┤        └────────────┘
                      │               │
                      └───────────────┘

If you already have duplicates, Access has a "Find Duplicates Query Wizard" (Ribbon: Create > Query Wizard).