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 junc

Solution 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.

Solution 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.

Solution 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)