Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    34

    Unanswered: SQL Query For Duplicates

    Hi! I'll try to explain this the best I can.

    I have two files. Let's say the first has City State and Zip and the second has customer records.

    File1
    City State Zip
    Richfield OR 48858
    Richfield OR 15448
    Richfield OR 88788
    AnyCity WA 78998
    AnyCity WA 44554

    File 2
    Customer City State Zip
    AnyCus Richfield OR 48858
    AnyCus2 AnyCity WA 44554
    AnyCus3 AnyCity WA 78998

    I would like the results to create one record for each zip in File1 where City and State from file1 = City and State from File2

    Results:
    AnyCus Richfield OR 48858
    AnyCus Richfield OR 15448
    AnyCus Richfield OR 88788
    AnyCus2 AnyCity WA 78998
    AnyCus2 AnyCity WA 44554
    AnyCus3 AnyCity WA 78998
    AnyCus3 AnyCity WA 44554

    Anyone have any ideas? I used a find duplicates query to pull all duplicates (file 2) and then I matched the results from that query to the records in File 1. That created a file with one record for each different zip from file 2 but obviously the origional zip codes were repeated for each record. So at this point I would have to go through my results and update the zip codes...this is the logic I came up with but I don't think you can do this with Access/SQL...

    WS-Ctr + 1
    Put City is WS-City
    Count City where WS-City = City
    Put Count is WS-CTR3
    WS-CTR = WS-CTR + WS-CTR3
    Set WS-CTR2 = 1
    In File2 Go to first recrod where City = File1_City(WS-CTR2)
    Set Zip in File2 = Zip from File 3 (WS-CTR2)
    Add 1 to WS-CTR2
    IF WS-CTR2 <= WS-CTR
    Go to next record
    Else
    Return to top.

    Is there a way or a stored function to do this? Thanks!
    "If you are good you will be assigned all the work. If you are really good you will get out of it."

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Holy christ on a pony!!

    You're thinking WAY to hard...

    Suck them both into tables, then:

    SELECT DISTINCT customer, city, state zip FROM f1 INNER JOIN f2 ON f1.city = f2.city AND f1.state = f2.state

  3. #3
    Join Date
    Nov 2003
    Posts
    34
    LOL. Thanks, you're the best!
    "If you are good you will be assigned all the work. If you are really good you will get out of it."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •