Trying to remove duplicate field1

[Guest]

New to Access 2002, trying to get a simple query to work.

Table1.field1 has no duplicates
Table2.field1 has duplicates

I want to end up with every record in Table1 where Table1.field1 has a match
in Table2.field1. I do not want duplicate field1 in Table1.

Why does this inner join result in duplicates in Table1?

SELECT table1.field1
FROM table1 INNER JOIN table2 ON table1.field1=table2.field1;

Tried a DISTINCT, but that only removes duplicate records, not duplicate
field1. I do not care which record it chooses but I just want unique
Table1.field1. So the followin does not work.

Select disctinct table1.field1, table1.field2
from table1
where table1.field1 = table2.field2

Thanks

Brenda

 

[Ken Snell [MVP]]

Query returns a record for each match. Because there are two matches if
table2 has duplicate records, you get two records.

Try this:

SELECT table1.field1
FROM table1 IN
(SELECT DISTINCT table2.field1
FROM table2);