Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Oct 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    find matches (access 2000)

    Edited by HansV to remove lots of superfluous line ends

    Is there a way in Access to find matches in the company names between 2 tables that are not related?
    My first table consists of the following fields:
    CustomerID - the field is autonumber
    CompanyName

    The second tables cosnsits of the folowing fields:
    ContactID the field is autonumber
    CompanyName

    I want to make a query enumerating only those customers and contacts, which COmpaneName is the same.

    For examplem:
    <pre>CustomerID companyname contactID companyName
    142 Fenchel 35 Fenchel
    </pre>

    There was a wizard in the query for find matched query but my programme cannot use it.
    Could you help me with an sql, if my wish is possible at all?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: find matches (access 2000)

    You could try

    SELECT Table1.CustomerID, Table1.CompanyName, Table2.ContactID
    FROM Table1 INNER JOIN Table2 ON Table1.CompanyName = Table2.CompanyName

    where Table1 and Table2 are the names of the tables. If one of the table or field names contains spaces, you must place square brackets around the name, for example
    <!t>[Table 1].

    The easiest way to find the SQL for such a query is to create the query in design view, test that it works correctly, then select View | SQL.

    PS It is not necessary to break lines in a post in the lines, the browser will take care of that automatically.

  3. #3
    New Lounger
    Join Date
    Oct 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: find matches (access 2000)

    Superb ! I got the query i wanted exactly in the format i needed ! How can i convert this query into a delete query to delete all the contacts from the table 2 whose company names match those in the table 1?

    Thank you in advance

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: find matches (access 2000)

    Try

    DELETE Table2.* FROM Table2
    WHERE Table2.CompanyName In (SELECT Table1.CompanyName FROM Table1)

Posting Permissions

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