Look for value to second table

[dhen21dx]

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.

 

[ranman256]

Did you join the 2 tables in the query?

 

[dhen21dx]

Did you join the 2 tables in the query?

Yes i joined the 2 table, i used left join where Table_A is the leading.

 

[welshgasman]

If you have multiple records in TableB for a record in TableA, then you need to specify additional criteria?

 

[xenou]

Table B should have Field_ID as primary key.

 

[dhen21dx]

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]

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.

 

[dhen21dx]

This is a sample. I tried the exist but seems is only doable in SQL.

 

[xenou]

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.