Quick way to find a Dup. data entry before Insert.

[PogoWolf]

Is there a quick way though a query (Hoping to combine an Insert with this information) to check for duplicate records in an access database before or during an insert?

I would only like to insert data that isn't already in the database. Currently, I'm checking for this data (useing a query) and if it's not there (.HasRows = False) then I insert the data.

I'm finding with the 100's of records, and loops required for all the data that's coming in, it's really slow to do it this way..

is there a better way?

For example:
for this data (that is already in the database)

AutoID | Date_Time | UserID | Question | Answer
----------------------------------------------------------
1 | 01/16/05 | 6 | 5 | 6
2 | 01/10/05 | 2 | 4 | 4
3 | 01/05/05 | 5 | 4 | 2
4 | 01/01/05 | 6 | 2 | 7

I want to insert this data into the table above:

Date_Time | UserID | Question | Answer
----------------------------------------------------------
01/16/05 | 6 | 5 | 6
01/01/05 | 6 | 2 | 7
01/01/05 | 3 | 2 | 7

in the data above, only row 3 would be inserted because recoard 1 and 2 are Dups, because the Date_time and the User ID match what's already in the database.

The PogoWolf

http://pogowolf.GamersCommune.com

 

[PHV]

Create a saved query named, say, qryGetNewRecords:
SELECT A.Date_Time, A.UserID, A.Question, A.Answer
FROM tempTable A LEFT JOIN yourTable B
ON A.Date_Time=B.Date_Time AND A.UserID=B.UserID AND A.Question=B.Question
WHERE B.AutoID Is Null;

Then your append query:
INSERT INTO yourTable (Date_Time, UserID, Question, Answer)
SELECT * FROM qryGetNewRecords;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PogoWolf]

Thanks!! I'll give it a shot!


The PogoWolf

http://pogowolf.GamersCommune.com