Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2002
    Posts
    2

    Question Unanswered: duplicate values :confused:

    Hi,

    I am trying to find a duplicate values from a column in a table.

    For ex.

    I want only the matching first 3 chars or number in a columns.

    1) 140 Lincoln Ave.
    2) 140 George St.

    It should return me only those rows matching the first three chars/nums

    Is it possible to write a query like this or any vba code

    Can you give any hints/ideas/sample code?

    Thanks

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    In your where clause, use the like keyword.

    e.g. select * from table where col like '140*'

  3. #3
    Join Date
    Jul 2002
    Posts
    2

    Question duplicate values

    Hi
    thanks for reply but that will be only the case where string starts with "140"

    i want to search for whole table where the first 3 char matches

    Maan

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    To clarify - you are not looking for a specific value but all records that have the 1st 3 characters that match ? Which version of access are you using ? How many rows are in the table you want to compare ?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select the duplicated values

    select left(charcol,3) from yourtable
    group by left(charcol,3)
    having count(*)>1

    or, if you want details of the rows that have the duplicated values,

    select * from yourtable
    where left(charcol,3) in
    (select left(charcol,3) from yourtable
    group by left(charcol,3)
    having count(*)>1)

    rudy
    http://rudy.ca/

Posting Permissions

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