MS ACCESS, Compare two similar tables

Access Discussion in 'Microsoft Office' started by Alexander R.A, May 17, 2011.

  1. Alexander R.A

    Alexander R.A

    Joined:
    May 17, 2011
    Likes Received:
    0
    Hi,
    I need to compare to tables where there can be data not 100% identical:

    We have two tables,Table1 and Table2 both with columns Product name and Producer.
    Table 1 is the "master" table.
    The data can be showed in table1 as:
    Product name: AAA
    Producer: GXL

    In table the data can be shown as:
    Product name: AAA or AAA_A or A_AAA
    Producer: GXL

    I need to find all AAA in table2 that exists in table1.

    I have just started to use ACCESS and need someone that can please explain it for me in details:bow:

    THX a lot, best regards Alex
     
    Last edited: May 17, 2011
    Alexander R.A, May 17, 2011
    #1
    1. Advertisements

  2. Alexander R.A

    LynnTroubleshooter

    Joined:
    May 24, 2011
    Likes Received:
    0
    In the second table, make another field (NewField) to store the 3 letter code after stripping off the _A and A_ from the original table (OldField).
    Make an update query to update NewField with the contents of OldField (basically, data backup).
    Make an update query with a calculated field. BeginUnderscore:left([NewField],2) with a criteria of "*_*" In this query, you will update Newfield with a value of right([oldfield],3).
    Make a second update query to check for ending underscores, just reverse the above.
     
    LynnTroubleshooter, May 24, 2011
    #2
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. allisond

    Access MS Access 2000 to MS Access 2002 problem

    allisond, Mar 23, 2006, in forum: Microsoft Office
    Replies:
    0
    Views:
    273
    allisond
    Mar 23, 2006
  2. silverstriip

    Access MS Access retrieving data from unlinked tables

    silverstriip, Dec 13, 2007, in forum: Microsoft Office
    Replies:
    0
    Views:
    400
    silverstriip
    Dec 13, 2007
  3. fibreperson

    Access MS Access 2007 - querying several tables

    fibreperson, Jan 1, 2009, in forum: Microsoft Office
    Replies:
    5
    Views:
    337
    fibreperson
    Jan 4, 2009
  4. shailesh_bali

    Excel MS excell File Error, MS Win XP Professional Version 2002

    shailesh_bali, Nov 17, 2009, in forum: Microsoft Office
    Replies:
    1
    Views:
    558
    Chuck D
    Jan 3, 2010
  5. p_slim
    Replies:
    0
    Views:
    1,306
    p_slim
    Sep 13, 2010
  6. trishcollins
    Replies:
    2
    Views:
    1,062
    trishcollins
    Mar 23, 2012
  7. Dodge

    Excel Creating two adjacent Y axes in MS Excel 2010

    Dodge, Oct 1, 2012, in forum: Microsoft Office
    Replies:
    3
    Views:
    1,462
  8. NickCee

    Excel MS Excel - MS Office 2010

    NickCee, Jul 11, 2016, in forum: Microsoft Office
    Replies:
    0
    Views:
    542
    NickCee
    Jul 11, 2016
Loading...