Look for value to second table

dhen21dx

Board Regular
Joined
May 10, 2013
Messages
128
Hello guys,

I need your opinion on how can i manage to look up a value from 1 table to another. Let say i have Table_A(field_ID) and Table _B(field_ID,field_Des). I want to look up Table_A(field_ID) to Table_B(field_ID), then i will check on Table_B(field_Des) let say my criteria is like "AA*", the result is only Yes or No. I have created query however, if i have 1 item in Table_A and 3 item in Table_B, it will result 3 times as well.
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
822
If you have multiple records in TableB for a record in TableA, then you need to specify additional criteria?
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,676
Office Version
2013
Platform
Windows
Table B should have Field_ID as primary key.
 

dhen21dx

Board Regular
Joined
May 10, 2013
Messages
128
Table B should have Field_ID as primary key.
Hello,

No Table_B has no primary key, it has the list of transaction with that ID's, so it's just look up if the ID is present and if field has a value of my criteria then it will say Yes to my query.
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,676
Office Version
2013
Platform
Windows
Not sure what you mean. You may want to give some sample data. If you simply want to know if an ID exists in a table then you could use an EXISTS() clause in your query.
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,676
Office Version
2013
Platform
Windows
Yes, I did mean in SQL. Sorry I didn't realize you meant an actual lookup field in a table. I thought you meant lookup in a generic sense. I personally would not use lookups in tables. I'm not sure how complicated they can be but the normal case is to lookup something in a lookup table (like lookup ProductID and return Product Description). Generally that means there is a primary key - foreign key relationship between the two tables. So my advice is If you have complicated things to show as data in table fashion, use a form in datasheet view, which can be backed by a query or adapted with all the flexibility that is available to you in forms.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,695
Messages
5,488,351
Members
407,633
Latest member
ps01

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top