Reputation: 11
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
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
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