Look for value to second table

dhen21dx

Board Regular
Joined
May 10, 2013
Messages
125
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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
809
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,649
Office Version
2013
Platform
Windows
Table B should have Field_ID as primary key.
 

dhen21dx

Board Regular
Joined
May 10, 2013
Messages
125
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,649
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,649
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,100,033
Messages
5,472,105
Members
406,802
Latest member
Jakub3

This Week's Hot Topics

Top