locked
Lookup & Match in Access RRS feed

  • Question

  • I'm using MS Access 2016 and need to perform some looks and match to separated tables. Table1 have a list of all Products and description but Table2 have a list of area owners based on two columns in it (room and manager). I need that Table1 list all the area owners from Table2 by matching rooms and managers in both tables.
    Friday, May 25, 2018 5:00 PM

Answers

  • Thank you so much, you're a genius!

    I missed to double check the column formats. I set them to Number and it was fixed!!!

    I have another question, I have to open other question? is related to assign value to a field based on column criteria.

    Thanks!

    Hi,

    Congratulations! Glad to hear you got it to work. It might be a good idea to start a new thread for your other (new) question.

    Cheers!

    • Marked as answer by Frank3623 Friday, June 8, 2018 3:52 PM
    Friday, May 25, 2018 8:15 PM

All replies

  • Hi Frank,

    Have you tried creating a Query? Open a query in Design View, drag/add both tables on the design grid and link the matching fields between the two tables. Then, add/drag the owner field from one of the table onto the query grid at the bottom.

    Hope it helps...

    Friday, May 25, 2018 5:20 PM
  • Thanks for your reply theDBguy, I just did it and it displayed the following error: "Type mismatch in expression" I think the reason is that the table2 have just a reference list of few records and the table2 have lot of records but both have the two reference mentioned (room and manager).
    Friday, May 25, 2018 5:32 PM
  • I need that Table1 get populated with the reference form Table2, in excel I used Index and Match function.
    Friday, May 25, 2018 5:33 PM
  • Hi,

    Are you able to share a copy of your database with test data?

    Friday, May 25, 2018 5:36 PM
  • Table2 Sample ------------------------------------------------   
       
    Material Room Manager Need Area Owner?
    1014181 105 Manuel 
    1012856 500 Pedro 
    1014600 500 Pedro 
    1017278 105 Manuel 
    3274500 500 Pedro 
    1016096 500 Pedro 
    3434222 325 Jose 
    34343434 105 Manuel 
    345345 500 Pedro 
    13676483.44 105 Manuel 
    15379057.64 500 Pedro 
    17081631.84 105 Manuel 
    18784206.04 500 Pedro 
    20486780.24 105 Manuel 
    22189354.44 500 Pedro 
    23891928.64 105 Manuel 
    25594502.84 500 Pedro 
    27297077.04 500 Pedro 
    28999651.24 105 Manuel 
    30702225.44 500 Pedro 
    32404799.64 105 Manuel 
    34107373.84 105 Manuel 
    35809948.04 500 Pedro 
    37512522.24 500 Pedro 
    39215096.44 105 Manuel 
    40917670.64 500 Pedro 
       
    Table 2 Sample ----------------------------------------------------------   
       
    Room Manager Area Owner 
    103 Jose Test1 
    105 Manuel test2 
    500 Pedro test3 
    250 Angel test4 
    25 Tito test5 
    30 John tes6 
    325 Jose Test7 
    Friday, May 25, 2018 5:57 PM
  • Okay, based on what you posted, if the result for the first record in Table1 should be:

    Material

    Material   Room   Manager   Owner
    1014181    105    Manuel    test2

    then try the following query:

    SELECT Table1.Material, Table1.Room, Table1.Manager, Table2.[Area Owner]
    FROM Table1
    INNER JOIN Table2
    ON Table1.Room = Table2.Room
      AND Table1.Manager = Table2.Manager

    Hope it helps...

    Friday, May 25, 2018 6:19 PM
  • This look very promising, only a syntax error away. Can you see something wrong with this expression? the message leave the cursor at the end of the expression.

    SELECT [Open TOs].Material, [Open TOs].[Dest Storage Type], [Open TOs].[Source Storage Type], Owner.[TO Owner]
    FROM [Open TOs]
    INNER JOIN Owner
    ON [Open TOs].[Dest Storage Type] = [Owner].Destination  
     AND [Open TOs].[Source Storage Type] = Owner.[Source Storage Type]

    Friday, May 25, 2018 6:58 PM
  • Hi,

    I wonder if "Owner" is a *reserved* word. Try enclosing it in brackets. Also, make sure the data types match between [Dest Storage Type] and [Destination], also in [Source Storate Type] for both tables.

    Hope it helps...

    Friday, May 25, 2018 7:10 PM
  • This is a similar OpenTO

    Material Source Storage Type Dest Storage Type
    1314881 103 200
    1912856 103 200
    1344600 103 200
    1017278 103 200
    3346500 103 200
    1016096 103 200
    1016097 103 200
    1014525 103 200
    3005081 200 812
    3058381 200 812
    3035081 200 812
    3702281 200 812

    This is a similar OwnerTO (I've changed the name)  
      
    Source Storage Type Destination TO Owner
    103 200 A-testing
    200 812 B-testing

    Friday, May 25, 2018 7:32 PM
  • Hi Frank,

    It shouldn't matter, the syntax would be the same either way. You just need to fix any errors based on misspellings or incompatible data types.

    Can you share the actual db files?

    Friday, May 25, 2018 7:51 PM
  • Thank you so much, you're a genius!

    I missed to double check the column formats. I set them to Number and it was fixed!!!

    I have another question, I have to open other question? is related to assign value to a field based on column criteria.

    Thanks!

    Friday, May 25, 2018 8:07 PM
  • Thank you so much, you're a genius!

    I missed to double check the column formats. I set them to Number and it was fixed!!!

    I have another question, I have to open other question? is related to assign value to a field based on column criteria.

    Thanks!

    Hi,

    Congratulations! Glad to hear you got it to work. It might be a good idea to start a new thread for your other (new) question.

    Cheers!

    • Marked as answer by Frank3623 Friday, June 8, 2018 3:52 PM
    Friday, May 25, 2018 8:15 PM
  • Hello Frank3623,

    I'm glad to hear that your issue has been resolved. I would suggest you mark any helpful reply or your solution to close the thread. For your new issue, as theDBguy said, please feel free to post a new thread for it.

    Thanks for understanding,

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Frank3623 Friday, June 8, 2018 3:52 PM
    • Unmarked as answer by Frank3623 Friday, June 8, 2018 3:52 PM
    Tuesday, May 29, 2018 1:49 AM