Hi Folks

[GoatieEddie]

Hi all,

I have a query that takes one of the fields and using a rather complicated expression creates a new field that holds a number.

I want to use this number to look up a value in another table so I get a phrase appearing in a third column. (I can then hide the second column and just show the text). But I can't work out how to connect the third table with the expression. I have been able to do it if I save the first query and write a second one as then you can use Expr1 (or whatever) in the criteria box but I was really hoping to do it all in one go.

Any ideas?

Thanks,
GE

 

[GoatieEddie]

Ooops - sorry about the subject bar. Anyway of changing it?!

 

[PHV]

Why not posting the SQL code you have so far and explaining us where you're stuck ?
My guess:
SELECT A.Field, B.Field
FROM someTable AS A, anotherTable AS B
WHERE B.someField = (your rather complicated expression)

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

 

[GoatieEddie]

Here it is:

SELECT DISTINCT [Client Changeable Data].[Model Name], {complicated expression} AS [Model Type Code]
FROM [Client Changeable Data], [Client Type Lookup];

So Model Type Code is what I want to look up in the "Client Type Look Up Table", match it against "Model Number" and return "Client Type"

I am about to leave now to see Star Wars but think I understand what you mean and will give it a shot tomorrow if my above little snippet doesn't inspire you further!
Thanks.