query for selecting related image

[masoud_sedighy]

Hello

I have 2 table,

Table1 has fields
docno (text)
title (text)
progress (number)

sample data is like below:

doc-001 test1 90%
doc-002 test2 25%

Table2 has fields

sample data is like below:

id (number)
icon(attachment type)

1 image1
2 image2

now I would like to make a query when progress of each docno is equal and more than 50% it uses image1 And when progress is less than of 50% it uses image2

Doc-001 test1 90% image1
Doc-002 test2 25% image2

thanks

 

[madefemere]

Hello,

Something like this ?

 SELECT Table1.docno, Table1.title, Table1.progress, IIf([progress]>0.5,DLookUp("[Icon]","[Table2]","[id]=1"),DLookUp("[Icon]","[Table2]","[id]=2")) AS UrlImage
FROM Table1;

 

[masoud_sedighy]

thanks, just problem is it returns just filename (text type) for the icon field, i need in this query attachement type, i checked somewhere and below query solved my problem:

SELECT Table1.*, Table2.icon
FROM Table1, Table2
WHERE (((IIf([progress]>=0.5,1,2))=[id]));