Append Block If Same

[Terri]

I have these two tables, one temp, one working and an
append query that contains five fields:
From Table: "Temp Table"
Plant CurYear CurMonth Time Used
Cin 2003 8 20000 18000
Cin 2003 8 20000 16000
Cin 2003 8 20000 19000
Cin 2003 8 20000 17500

Target Table: "Working Table"
Plant CurYear CurMonth Time Used
Cin 2003 8 20000 18000
Cin 2003 8 20000 16000

I need to use the append query to block the two similar
records in both tables from being re-appended, only
allowing the new record(s) to be added from the "Temp
Table" to the "Working Table". More fields under similar
append conditions could be added to both tables as this
application goes forward. Any Ideas would be appreciated!

Oh, I'm kinda new at this as you can see....

Thanks,

 

[John Spencer (MVP)]

Simplest method might be to create a unique index on the fields that have to be
unique in combination. This would prevent the append from happening on those
records and give you a warning message telling you that.

You could also write your query to eliminate the duplicates. AIR SQL (untested) follows

INSERT INTO WorkingTable
SELECT W.* FROM
WorkingTable As W Right Join TempTable as T
ON W.Plant = T.Plant
AND W.CurYear = T.CurYear
AND W.CurMonth = T.CurMonth
AND W.[Time] = T.[Time]
WHERE W.Plant Is Null

To create a multiple field unique index
--Open up the WorkingTable in design mode
--Select View: Index from the menu
--Enter Name for Index in first blank cell 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
--Continue moving down and selecting fieldnames until all needed are included.
--Close the index window and close and save the table

Now use your existing Append query. You will get a message saying that a number
of records couldn't be added due to validation rule violations.