Get value of a specific field from a set of fields

[mshakeelattari]

I have a Table1:

========================================
ID    Opt1    Opt2      Opt3       Opt4
========================================
1     xxxx    yyyy      zzzzz      aaaaa
2     kkkkk   llllll    mmmmm      zzzzz
3     bbbbbb  cccccc    dddddd     eeeee
 
 

Another Table2:

===========
ID    Opts
===========
1     Opt2
2     Opt4
3     Opt1
 
 

I want a query to give:

=============
ID    Values
=============
1     yyyy
2     zzzzz
3     bbbbbb
 
 

or a combination:

====================================================
ID    Opt1    Opt2      Opt3       Opt4      Values
====================================================
1     xxxx    yyyy      zzzzz      aaaaa     yyyy
2     kkkkk   llllll    mmmmm      zzzzz     zzzzz
3     bbbbbb  cccccc    dddddd     eeeee     bbbbbb
 
 

Please note that the values of field ID from both tables are same.

Please help me. Thanking in anticipation.

[gnawoncents]

Try this as the SQL for your Query:

SELECT Table2.ID, DLookUp([Table2].[Opts],"Table1","[Table1].[ID] = " & [Table2].[ID]) AS [Values]
FROM Table2;
 

[mshakeelattari]

Nice. It worked for me! Thank you.

[gnawoncents]

You are quite welcome!