Results 1 to 2 of 2
  1. #1
    johnmerlino is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    97

    split table, compare its splitted fields with another table, and then append content

    Hey all,

    I have this query, but it's giving me syntax errors:

    SELECT s.fullname INTO SummaryJudgment_FinalForgottenWithMiddle
    FROM (
    SELECT(
    (SELECT Left([aname],InStr(1,[aname],",")-1)) As lastname FROM
    SummaryJudgment_FinalForgotten) & " " & (SELECT
    RIGHT([aname],InStr(1,[aname],",")+1)) As firstname FROM
    SummaryJudgment_FinalForgotten) & " " & (SELECT
    summary_judgment.middle_initial AS middlename FROM summary_judgment)
    ) AS fullname
    ) AS s
    FROM SummaryJudgment_FinalForgotten AS f INNER JOIN summary_judgment
    AS s ON f.lastname = s.last_name && f.firstname = s.first_name



    Basically this is what two tables look like (note they will have more fields than 1 where last or first name of different fields can be similar):

    SummaryJudgment_FinalForgotten (table)
    aname (field)
    Leventhal,Raymond (data)


    summary_judgment (table)
    first_name(field)
    Raymond (data)
    last_name (field)
    Leventhal (data)
    middle_initial (field)
    P (data)

    Ultimately, I'm trying to create a new table that is like
    SummaryJudgment_FinalForgotten but with the middle initial from
    summary_judgment appended:
    Leventhal,Raymond P

    Thanks for response

  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    it's so complicated, please check the syntax carefully.
    if you can not find any error, try to split the query by saving sub queries as a queries then use the saved queries instead of sub queries. and then you will find out the error point.

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

Similar Threads

  1. using list box to append whole table
    By shenix2005 in forum Programming
    Replies: 4
    Last Post: 08-20-2010, 08:49 AM
  2. Replies: 2
    Last Post: 07-26-2010, 11:28 AM
  3. Loop through a list of table names and compare
    By mikneus in forum Programming
    Replies: 1
    Last Post: 05-21-2010, 10:36 AM
  4. Replies: 3
    Last Post: 05-19-2010, 10:08 PM
  5. Replies: 0
    Last Post: 08-01-2009, 12:43 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