Query creates redundant entries

问题:

I've been using MS Access 2010 for a while now and I have big problem. I'll attach a dummy database here for reference. It's simple, three tables: 2 for data and 1 to act as a junction for a many-to-many relationship. I have one query that I would like to be able to enter values. Lets say I enter the following:

+-------------+-------------+
|    NameA    |    NameB    |
+-------------+-------------+
|    Frank    |    Bob      |    
|    Frank    |    Harry    |
|    Tom      |    Harry    |
+-------------+-------------+

The 2 data tables will end up with redundant entries. It's as if the query simply adds a new entry regardless of what is already in the tables. How do I fix this?

Thanks in advance.

Update: Per request here is the query code

SELECT Table1.NameA, Table2.NameB
FROM Table2 
INNER 
JOIN (Table1 INNER JOIN Table3 ON Table1.ID = Table3.IDA) 
ON Table2.ID = Table3.IDB;

回答1:

I've found an answer here.

The key points are as follows:

1. The builtin functionality provided to the query via DataSheet View is very simple. It only adds new records regardless of creating redundant entries.

2. The desired functionality cannot be easily implemented on the Query/Table level. Hence I've implemented it on the Form/Report level. Personally, I could see no way in theory to implement it on the Query/Table level, but I'm certain there are VBA gurus out there who can do anything.

3. Designers can use a Combo Box for each desired field in the many-to-many relationship. This gurantees no redundant entries. Then to add new entries VBA code has to be added to the "On Not In List" property of the Combo Box. See link above.

Hope this helps someone in the future. I know I was happy. Thanks for the help.

回答2:

sounds like the Example from w3schools.com when you do your inner join it grabs information from both tables where any column Matches. I assume that there are other columns in both Tables.

回答3:

Typically, a cross-reference table would join Table1 and Table2 like:

SELECT Table1.Name1, Table2.Name2
FROM ((Table1 INNER JOIN TableRef ON Table1.ID1 = TableRef.ID1) INNER JOIN Table2 ON Table2.ID2 = TableRef.ID2)

Try:

SELECT DISTINCTROW Table1.Name1, Table2.Name2
FROM ((Table1 INNER JOIN TableRef ON Table1.ID1 = TableRef.ID1) INNER JOIN Table2 ON Table2.ID2 = TableRef.ID2)