Query to copy distinct records - no duplicates

To answer your last question: by setting a unique index on that field, or combination of fields. This is a really important rule in database design: restrict as much as possible. In addition to unique indexes, this rule implies you make fields required that logically should be required, and choose the smallest data type that fits the expected data.

To insert distinct records, create an append query along these lines:

insert into tableDestination(field1, field 2, ...)
select distinct fieldA, fieldB, ... from tableSource;

If you are unfamiliar with how to create a unique index on multiple columns, as an example it might be necessary to create a unique index on the EmployeeID, ProjectID and CityID columns in a ProjectEmployees table to prevent the same employee being assigned to the same project in the same city more than once. This is done in table design view by first selecting Indexes on the Design ribbon. In the dialogue which opens enter a suitable name for the index in the first empty row of the Index name column, ProjectEmployee say. In the Field Name column of the same row enter ProjectID, and with this row still selected, in the Index Properties below, select 'Yes' as the Unique property. Then enter EmployeeID in the Field Name column in the row immediately beneath ProjectID. Don't enter anything in the Index name column for this row. Then enter CityID in the Field Name column in the row immediately beneath EmployeeID. Again, don't enter anything in the Index name column for this row. Close the dialogue and save the table design.