Append Query, but don't append duplicates

[briandh]

Hello,
I have a database and I have one table that holds all historical data on test scores(named [SubSheet PN Test Scores]), and one table that holds the current year's test scores(named [Table NCLEX Quarterly Totals]). The contents of [Table NCLEX Quarterly Totals] are updated manually throught the year, and at the end of the year, the results from this table are appended to the historical table. I have an append query that will do this, and it works fine (Here is the SQL):
INSERT INTO [SubSheet PN Test Scores] ( ID, [Number of Program Candidates], [Percent Program Candidates Passing], Form, [Date Series Written] )
SELECT [Table NCLEX Quarterly Totals].[ID], [Table NCLEX Quarterly Totals].[TotalNumberCandidates], [Table NCLEX Quarterly Totals].[TotalPercentPassing], [Series Title] AS Series, [Year] AS [Date]
FROM [Table NCLEX Quarterly Totals]
This works fine, but the problem is, if this append query is run more than one time it will insert duplicates for each time you append. My question is this: Is there a way to check to see if [Date Series Written] contains this year's results already before I run the append query. If it detects that this year's results have already been appended, then there is no need to append again, and can alert the user to this fact. Does this make sense?

Thanks in advance, and let me know if you need clarification.

Thanks,

Brian

 

[tracy1234]

Brian -

Here are a few ideas to help prevent duplicates:

1) Create primary keys that disallow duplicates in the tables. Users will get a message saying that the data was not appended due to key violations.

2) Create a form that pops up when the users try to append new data listing some critical details of the last batch that was appended.

3) Use the find duplicates query wizard to create a report that warns users about potential duplicates. You could have the users "pre-append" the records to a log file which lists all records previously created and run the duplicates query,