1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Rep Power

    sql comparison between two rows


    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!!
  2. #2
  3. Contributing User
    ASP Adventurer (500 - 999 posts)

    Join Date
    Jun 2011
    South Jersey
    Rep Power
    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.

Similar Threads

  1. Comparison function
    By webb in forum ASP Development
    Replies: 3
    Last Post: November 30th, 2010, 07:33 AM
  2. Find duplicate rows (and show all rows, no grouping)
    By garyrobar in forum SQL Development
    Replies: 1
    Last Post: December 16th, 2008, 01:04 PM
  3. Replies: 3
    Last Post: October 25th, 2005, 08:01 AM
  4. comparison
    By rabia in forum ASP Development
    Replies: 1
    Last Post: March 22nd, 2004, 04:39 AM

IMN logo majestic logo threadwatch logo seochat tools logo