Lookup a value with a calculated foreign key

[62chev]

I am trying to lookup a value in a table based on a calculated key.

Example: I have several parts in a table, some of these parts have a specific suffix (-SP) I want to lookup the same part in a table without the -SP.

Part Value Calculated Key
12345-SP 12345
22233-SP 22233
34555-SP 34555

With the calculated key I want to lookup a value in another table.

I'm probably missing the obvious... I would like to do it in a query as I can't alter the table definitions. I have tried Dlookup without success.

Any help would be greatly appreciated.

 

[DotNetGnat]

you have to do this...

left(PartValue, len(PartValue)-3)=CalculatedKey

-DNG

 

[GingerR]

Say you have Table1 with the PartValue (with the -SP) and Table2 with field Field1 (without the -SP).

Query =

SELECT Table2.*
FROM Table1, Table2
WHERE (((Table2.Field1)=Left([PartValue],InStr([PartValue],"-")-1)))

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[PHV]

Another way:
SELECT Table1.*, Table2.*
FROM Table1 INNER JOIN Table2 ON Table1.Part = Table2.Part & '-SP'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[62chev]

Thank you for the quick response - you guys are great! One thing I failed to mention is sometimes I need to lookup this other field value in the same table (so table1 and table2 are the same).

Your solutions work when I am looking at a different table, how would I look back in the same table?

Thanks again.

 

[PHV]

SELECT A.*, B.*
FROM TableName AS A INNER JOIN TableName AS B ON A.Part = B.Part & '-SP'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[62chev]

Thanks - Works great! Appreciate the assistance.