sql query distinct join

Sorry I missed and deleted earlier question on accident again.

I have a situation, I am trying to select distinct values from table 1 that are new and store them in table 2. The problem is that table has duplicates on column "name" but开发者_JAVA技巧 it does have a key column "id", but the different ids of course map to the same name.

My idea on the query would be

INSERT INTO TABLE2 
(NAME, UniqueID) 
  SELECT DISTINCT TABLE1.NAME, TABLE1.ID 
  FROM TABLE1 
  LEFT JOIN TABLE2 ON TABLE1.ID=TABLE2.UniqueID 
  WHERE TABLE2.NAME IS NULL 

Need help on getting the query to return my desired results, right now it still produces duplicates in table2 (on name column), which I don't want. I would want it to only append new records even if I run the query multiple times. For example if two new records were added into table1 but one has the name already in table 2, then the query would only add 1 new record to table2

just a note: I am using ms access, so it has strict syntax on single queries

EDIT:

Folliwing input I had came with this query

INSERT INTO TABLE2 
(NAME, UniqueID) 
  SELECT TABLE1.NAME, Min(TABLE1.ID) 
  FROM TABLE1 
  LEFT JOIN TABLE2 ON TABLE1.NAME=TABLE2.NAME 
  WHERE TABLE2.UniqueID IS NULL 
  Group By TABLE1.NAME;

but these actually had to be separated to two separate wueries in access to run without a reserver error flag but now I ran into additional problem. When I run the two separate queries, it works fine the first time, but when I run it twice trying to test to see if any new records have been added to table 1, it then appends 1 record when no new records are in table 1, so it appends a blank name value and a duplicate unique id, and continually does that same process everytime I run it.

Since you're pulling both Name and ID, the distinct keyword will only pull distinct combinations of those. Two records with the same Name and different ID's is still valid.

In the case of two Names with different ID's, which would you like to be inserted?...

insert into table2 (Name, UniqueID)
select t1.Name, MIN(t1.ID)
from table1 t1
left join table2 t2 on t1.ID = t2.UniqueID
where t2.Name is null
group by t1.Name

in response to comments, I realize the Name field is what should be joined on, to prevent dupes that already exist.

insert into table2 (Name, UniqueID)
select t1.Name, MIN(t1.ID)
from table1 t1
left join table2 t2 on t1.Name = t2.Name
where t2.UniqueID is null
group by t1.Name

INSERT INTO TABLE2 (UniqueID, NAME)  
SELECT min(t1.ID) as UniqueID, t1.NAME  
FROM TABLE1 t1 
LEFT JOIN TABLE2 t2 ON t1.ID=t2.UniqueID  
WHERE t2.NAME IS NULL  
group by t1.NAME