Duplicate value check in 2 fields

[ajc718]

I import most data directly into tables. I know how to check dup in 1 field but is there a way to check for duplicate values based on to fields.

Al

 

[PHV]

SELECT A.*
FROM yourTable A INNER JOIN (
SELECT Field1, Field2 FROM yourTable GROUP BY Field1, Field2 HAVING Count(*) > 1
) B ON A.Field1 = B.Field1 AND A.Field2 = B.Field2

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

 

[ajc718]

I thank you for your help but I am not clear as to where I am suppose to make this change

 

[PHV]

This is SQL code you have to amend in the SQL view pane of the query window to suit your table and fields names.

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

 

[ajc718]

Normally i dont do a query to do this I just import directly into table. So my understanding is that if I want to add another record to the table and check to different fields for duplicate value it is best to add it thru a form and add the SQL statement in a query. There is no way just to have same effect by just importing an outside file directly into the table. But the reason i do it this way is I am adding 100-500 records at a time.

 

[PHV]

Simply create a composite unique index on the two fields.
(Note: an unique index is an index not allowing duplicates).

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

 

[ajc718]

I dont think i am explaining properly I need to match 2 fields to find a duplicate record. Please see example below:

field1 field2 field2 field4
7354 edrrt ertfd sw1 add this
7390 ftyui edrty sw1 add this
7390 edrft ghxsw sw2 cause field1 is the same but
field4 is different
7390 aaaaa bbbbb sw1 do not add because
field1 and field4
exsist already above

 

[PHV]

I know how to check dup in 1 field
How you did that ?

 

[ajc718]

I can check data in 1 field also but someone said it could be done on based on looking at 2 fields so thats why i am here. Thank you for your help anyway.

 

[PHV]

You didn't answer my question.
You claim you know how to check data in 1 field.
So, please, say us how you do that. This will help us explaining you (with your terms) how to do the same with 2 fields.

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