Append data / Prevent duplicates

[Nick X]

Hi all,
I am working across platforms with my data (ESRI/GIS and Access). I am
using Access for billing and archiving (and a lot of other cool tasks that I
don't have the patience to perform in ArcGIS). What iwant to do is take data
from my tblInspection (ArcGIS) and move it to tblInspection_Archive (Access).
My Key field is ParcelID (non-unique in archive) and I have a Round # field
(e.g.: 2009_R1, 2009_R2). I need to use these fields to create a unique key
so if someone tries to run the query multiple times on the same Round it will
error out.

SQL:
INSERT INTO tblVLM_Inspection_Archive ( TAXPINNO, Round, Photo1, Path1, CUT,
AMOUNT, LOTTYPE, EXCEPTIONC, DEBRISLOCA, INSPECTOR, K_PID, BULK_, ACCESS_,
TreeRemove, Photo2, Photo3, Photo4, INSDAT, INSPTIME, BRUSH, WC_Container,
PhotoEdit )
SELECT ... (clipped for brevity)
FROM GISADMIN_mv_VLM INNER JOIN tblVLM_Inspection_Archive ON
GISADMIN_mv_VLM.TAXPINNO = tblVLM_Inspection_Archive.TAXPINNO
WHERE (((GISADMIN_mv_VLM.TAXPINNO)=[tblVLM_Inspection_Archive].[TAXPINNO])
AND ((GISADMIN_mv_VLM.Round)<>[tblVLM_Inspection_Archive].[Round]) AND
((GISADMIN_mv_VLM.Photo1)<>"") AND
((GISADMIN_mv_VLM.Path1)<>[tblVLM_Inspection_Archive].[Path1]));

At this point it just keeps appending. Any help would be appreciated.

 

[Jerry Whittle]

In Access open up the table in question in design view. Make a unique indexed
based on the fields that would define what a duplicate is. When you try to
append dupes into Access, it will pop up a message saying that X number of
records weren't added due to a constraint error. If you don't want to see
this error message, turn Set Warnings to Off, run the query, then Set
Warnings back On. You can do this in a macro or code.

 

[Nick X]

Jerry,
Thanks for your quick response.

In Access open up the table in question in design view. Make a unique indexed
based on the fields that would define what a duplicate is. .

Are you saying to add a field into which I would append my concatenated
fields or is there a way to set this up in the Indexes dialog?

Thanks,
Nick

 

[John Spencer]

To create a multiple field unique index (Compound index)
--Open up the table in design mode
--Select View: Index from the menu
--Enter a name for the Index in first row under Index Name
--Select one field in the index under Field Name
--Set Unique to Yes
--Move down one line and select the next FieldName
(Do NOT skip rows, do NOT enter the index name again)
--Continue moving down and selecting fieldnames until all needed fields are
included (10 max).
--Close the index window and close and save the table

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

 

[Nick X]

You learn something new everyday...

Thanks,
Nick