CODEWITHSUNDEEP
databasems-access
Michael Simmies
Michael Simmies

Reputation: 11

access - Only append unique results

I have a system where new users can be added, and i want to make a query that checks to see if there are duplicate results with the same Name_ID and only adds the unique results. (More explained below)

Table1

Name_ID ¦ Name
-------------------
1       ¦ Michael
2       ¦ Rebecca
3       ¦ Sammy

Table2 

Name_ID ¦ Name
-------------------
1       ¦ Michael
2       ¦ Rebecca

These are in two separate database files as Table2 is supposed to be an archive. What i want to do is to use a query ONLY to append the Name_ID's that are unique from table1 to table2, i.e. only appending 3 but leaving out Name_ID 1 and 2.

One last thing, i dont want to use SQL for this solution but would like to know if MS-Access has any in-built features that can solve this.

EDIT: Apparently there is no solution for without SQL so how would this be achieved with SQL?

Upvotes: 1

Views: 85

Answers (2)

marlan
marlan

Reputation: 1485

The MS Access built-in feature to prevent duplicates is a No Duplicates index on the field. A primary key is a type of such an index. These indexes will prevent insertion of a record in case of a duplicate value on the indexed field. Please see more here

Upvotes: 1

Tedo G.
Tedo G.

Reputation: 1565

You should use this query:

INSERT INTO Table2(Name_ID, Name)  
SELECT Name_ID, Name  
FROM Table1 LEFT JOIN Table2 ON Table1.Name_ID = Table2.Name_ID  
WHERE Table2.Name_ID IS NULL

either, make a query or use Docmd.RunSQL command

Upvotes: 2

Related Questions