Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    String Compares (2003)

    Hi folks, I have a table I'm importing into a db. Table 1 contains individual's full names across 2 fields, e.g. forename might have "Maria Francesca Anna" and Surname might have "Della Antonio". Table 2 will just state "Maria Antonio". How can I go about comparing whether people in Table 1 are already in database Table 2 without delving too much into the extremities of code (I'm a beginner)???

    Thanks for your help! Ina

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

    Re: String Compares (2003)

    Does table 2 contain the name in a single field?

  3. #3
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: String Compares (2003)

    Hi. Database has Forename and Surname, completely missing out middle and additional names (e.g. Von, Van, Della, etc. etc.), which is concatanated into a single field Name, so there are a variety of related fields.

    Ina

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

    Re: String Compares (2003)

    Check out the attached database. It uses only queries, no VBA code. I have assumed that you want to match on the first word in the FirstName field and on the last word in the LastName field. You can change this to suit your needs.

  5. #5
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: String Compares (2003)

    Hi thanks - that looks good, not sure how its working exactly mind you, but should do the trick.

    Ina

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

    Re: String Compares (2003)

    The qryTable1 query uses the InStr function to determine the position of the first space in FirstName. If not found, this returns 0.
    Next, a combination of IIf (the version of If ... Then ... Else ... for use in expressions) is used to extract the first word (name) in FirstName.
    Similarly, InStrRev is used to determine the position of the last space in LastName.
    A combination of IIf and Mid is used to extract the last word (name) in LastName.
    These two are concatenated with a space in between to form the full name.

    The other queries use three different forms of joining qryTable1 to tblTable2:
    qryCommon has an inner join, returning all records that have FullName in common.
    The other two use an outer join to return all records with a name on one side that doesn't occur on the other side.
    Open one of these queries in design view and double click the join line between the tables to see the type of join.

Posting Permissions

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