sql comparison between two rows

[shachar]

Hello

I need some help with a query i'm trying to write but althogh it works on sql server, the query doesn't work in sql access.. so i would appricate your help.

my table is 300 rows and it looks like that:

ID	Name	City	ZipCode	Empty
1	Ben	Chicago	4321
2	Jerry	New York	4321
3	Jerry	New York	4321

the ID is the key.
I wish that when a row is similar to another row in columns Name,City and Zip (all of them) so one of them, doesn't matter which, will update the empty column to certain value.

for example, the result of this query shuold be

ID	Name	City	ZipCode	Empty
1	Ben	Chicago	4321
2	Jerry	New York	4321	value
3	Jerry	New York	4321

Thanks a lot!!

[meratigoerr]

What is the purpose for marking the duplicate? are you trying to delete the duplicates or just mark them as duplicates in your database?

I've done it using three steps. (there may be other ways to do it, this works for me and is fairly simple.)

1st: Create a tempory table. include two fields. an id field as number datatype, and a [combined] field as text datatype. Set the field [Combined] as the primary key.
2nd: Let's assume your table is called [YourTable]. Were going to create an append query to transfer all the records into your temporary table. You're going to combine all of fields you're trying to find duplicate of into one field. It will look something like this.

INSERT INTO TempYourTable ( Combined, ID )
SELECT [Name]+[City]+[ZipCode] AS Combined, YourTable.ID
FROM YourTable;

When you run the query, you will get errors. this is normal. it's copying only single records to the temp table, no duplicates will be copies because of the primay key.
3rd: Create a query to find any unmatched id's in your first table to your temp table and use an update query to add "Value" to your empty field.

UPDATE YourTable LEFT JOIN TempYourTable ON YourTable.ID = TempYourTable.ID SET YourTable.Empty = "Value"
WHERE (((TempYourTable.ID) Is Null));

the table can be permanant, just run the two queiries in order when you want to mark your duplicate fields. You can also use this to delete duplicate queiries, by using a delete query instead of an update query.