Home>

Always thanks for your help.
I'm using Access to create VBA and queries,
It is good if either one matches. I can't make an OR duplicate query.

For example, duplicate name, phone number, or email address.
That is.

It has the following configuration.

■ Table structure and data
Voucher number: Name: Phone number: email

111: Query Taro: 111-222-3333: taro @ examplecom
112: Access Jiro: 222-333-4444: jiro @ examplecom
113: Query Jiro: 111-222-0000: taro @ examplecom
114: Query Co., Ltd .: 111-222-3333: info @ compaycom
115: SQL Jiro: 999-999-9999: sql @ examplecom
116: Query Co., Ltd .: 000-000-0000: NULL

■ Index
Your name, phone number, and email.

Specifically, I would like to extract the following four items with a star.

111: Query Taro: 111-222-3333: taro @ examplecom
113: Query Jiro: 111-222-0000: taro @ examplecom → Duplicate email address
114: Query Co., Ltd .: 111-222-3333: info @ compaycom → Duplicate phone number
116: Query Co., Ltd .: 000-000-0000: NULL → Duplicate name

In other words, if any of the name, phone number, and email address match, it will be extracted.

Also, some data does not have an email address.
Even in this case, the names match, so I want to extract them. This is difficult.

I may use a subquery that I'm studying, but it's difficult.
I would appreciate any advice.

  • Answer # 1

    It means that you can't use only queries, so please try to tabulate the information once.
    Create a table with the following table creation query.

    select name into name_duplicate
    from tbl
    where name is not null
    group by name
    having count (*)>1
    select phone number into phone number_duplicate
    from tbl
    where phone number is not null
    group by phone number
    having count (*)>1
    select email into email_ duplicate
    from tbl
    where email is not null
    group by email
    having count (*)>1


    After executing each, execute the following SQL.

    select * from tbl
    where name in (select name from name_duplicate)
       or phone number in (select phone number from phone number_duplicate)
       or email in (select email from email_ duplicate)


    If the underlying table is an ODBC linked table, try copying that table as a local table and running it.

    OrPass-through queryIf so, I think that the result will be rejected even if the query is left as it is.

    select * from tbl
    where name in (
            select name from tbl
            where name is not null
            group by name
            having count (*)>1
          )
      or phone number in (
            select phone number from tbl
            where phone number is not null
            group by phone number
            having count (*)>1
          )
      or email in (
            select email from tbl
            where email is not null
            group by email
            having count (*)>1
          )
    Postscript

    I want to check this function (whether it is a repeater in real time) every time I move a record

    In that case, the comparison source should be limited, so I feel that there is no problem if the query is based on that condition.

    select * from tbl
    where slip number in (
            select slip number from tbl
            where Voucher number<>[Voucher number to be processed]
              and (
                     Name = [Name to be processed]
                  or phone number = [phone number to be processed]
                  or email = [email to be processed]
                  )
          )


    * The value in [] is the value of the corresponding record.
    If it's just a flag, I think the following is fine.

    DCount ("*", "tbl", "[conditions above]")>0

  • Answer # 2

    How about below?

    SELECT a. *
    FROM tbl slip AS a
    WHERE EXISTS (
      SELECT * FROM tbl slip AS b
      WHERE a. Slip number<>b. Slip number And
           (a. Name = b. Name OR a. Phone number = b. Phone number OR a.email = b.email)
    );

  • Answer # 3

    How to create with only Access functions

    Create three duplicate queries with the Duplicate Query Wizard.
    At that time, the output target is all applicable columns (slip number, name, telephone number, email).
    For example, name them "duplicate name", "duplicate phone call", and "duplicate email".

    Combine the above three duplicate queries with a union query.

    SELECT * FROM duplicate name
    UNION SELECT * FROM Duplicate phone
    UNION SELECT * FROM duplicate email
    ;

    How to create SQL using subqueries

    SQL to search for duplicate records

    Modified based on the third SQL introduced on the above page.

    SELECT * FROM table
    WHERE
      Name IN (
        SELECT name FROM table GROUP BY name
          HAVING COUNT (name)>1
      )
      OR
      Phone number IN (
        SELECT Phone Number FROM Table GROUP BY Phone Number
          HAVING COUNT (phone number)>1
      )
      OR
      email IN (
        SELECT email FROM table
          WHERE email IS NOT NULL GROUP BY email
          HAVING COUNT (email)>1
      )
    ;

    I'm sorry, but I haven't verified it because I don't have an Access environment at hand.