Results 1 to 7 of 7
  1. #1
    daveish is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Posts
    9

    Query part of a table field

    Hi



    I have an issue that has been driving me mad for the past few hours and i can't seem to find any solution.

    I have two tables that contain names and i want to run a query to show the ones that are common to both however one table has the names set out in four fields (Title, First Name, Middle Name, Last Name) where as the other table has just one field titled Name which contains everything.

    What i would like to do is ideally run a query that compares the fields First Name and Last Name with the field Name

    I have tried seperating the Name field out into seperate fields but this has proven most awkward due to the titles and people with two middle names.

    I'm afraid to say i am a complete newcomer to access and so the simplest the solution the better.

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    would be easier to concatenate the fields in the '4 field' table to match to the '1 field' table.

    SELECT *
    FROM tableA A, tableB B
    WHERE B.FullName=A.FirstName & " " & A.LastName

    This assumes that name spelling are the same (i.e. not Bob and Bobby), the full name does not include initials, etc

    a variation would be

    SELECT *
    FROM tableA A, tableB B
    WHERE B.FullName Like "*" & A.FirstName & "*" & A.LastName & "*"

    which would match Bob Smith to Bob Smith but also Bobby J Smith, Bob Smithson, etc

  3. #3
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051

    Query part of a table field

    Something similar to

    Select *

    From tbl4names, tblname

    Where tbl4names.firstname =Mid([longname],1,instr([longname]," ")-1) and tbl4names.lastname = Mid([longname],Instr([longname]," ")+1)

    But please change the table names. No table or field should be called name. X



    Sent from my iPhone using Tapatalk

  4. #4
    daveish is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Posts
    9
    Quote Originally Posted by Ajax View Post
    a variation would be

    SELECT *
    FROM tableA A, tableB B
    WHERE B.FullName Like "*" & A.FirstName & "*" & A.LastName & "*"

    which would match Bob Smith to Bob Smith but also Bobby J Smith, Bob Smithson, etc
    Thanks for your help that certainly sounds like what i'm after.

    I've tried constructing this in the fashion you laid out but i'm getting an error message of Characters found after the end of SQL statement.

    This is what i put in. uk-500 is the table that has the fields of first and last names and Honours is the one with the combined name

    SELECT [uk-500].first_name, [uk-500].last_name, Honours.Full_Name
    FROM [uk-500], Honours;
    WHERE Honours.Full_Name Like "*" & uk-500.first_name & "*" & uk-500.last_name & "*"

  5. #5
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    ; should be at the end I think


    Sent from my iPhone using Tapatalk

  6. #6
    daveish is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Posts
    9
    I tried moving the semicolon to the end instead of after the FROM statement but now i get a Syntax error missing operation in query expression WHERE Honours.Full_Name Like "*" & uk-500.first_name & "*" & uk-500.last_name & "*"

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    FYI you don't need the semi colon

    the reason you code is failing is because your table names have illegal characters, the hyphen. So Access is trying to subtract 500 from uk. Best to remove them or change to underscore, but try putting square brackets around the name - [uk-500]

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 05-27-2015, 08:57 AM
  2. Query Part of a Field
    By EHittner in forum Queries
    Replies: 1
    Last Post: 01-31-2015, 07:28 PM
  3. How do I get the CREATE TABLE part from a table creation query?
    By Javier Hernández in forum Programming
    Replies: 5
    Last Post: 01-14-2014, 01:56 PM
  4. How Can export Large table part by part
    By shabar in forum Import/Export Data
    Replies: 2
    Last Post: 02-04-2013, 06:29 AM
  5. Query any part of the field
    By spleewars in forum Queries
    Replies: 1
    Last Post: 05-31-2012, 07:13 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums