Append Unique Records only

[ironwood9 via AccessMonster.com]

I have a table that I regularly append records to, and this is an automatic
process daily - the problem is, the recordset being used to append to my main
existing table always have a large percentage of duplicate records - the same
value for every single field.

My current workaround is to first create an empty shell table with a primary
key to accept only one unique record for each case of duplication, and after
first stripping any record out that has a duplicate, then taking that set of
unique records and appending them to my main table.

Is there a way to skip all these steps ?

 

[Gigamite]

Is there a way to skip all these steps ?

Apply a unique index on the columns you don't want duplicate values and
import directly into the table. Only the unique records will be saved
in the table.

 

[John Spencer]

It would help if you posted the query you are using (View: SQL and then copy
and paste).

Generically the SQL would look like:

INSERT INTO TargetTable (Field1, Field2, Field3)
SELECT DISTINCT Field1, Field2, Field3
FROM SourceTable

The Distinct will return one row for any number of rows that have identical
information in the fields listed.

An alternative would be to use an aggregate query which would allow you to
combine records based on some fields being identical and then choosing for any
other fields a more or less random value from that group or the largest or
smallest value from the group of identical records.

For example to import one record where field1 and field2 are the same in
multiple records and from that group of records you want the largest value of
field3 the query would be as follows:

INSERT INTO TargetTable (Field1, Field2, Field3)
SELECT Field1, Field2, Max(Field3) as LargestValueOfField3
FROM SourceTable
GROUP BY Field1, Field2

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County