Query to Lookup 2 fields on each record. Trouble with Dlookup and Joins

[waynemurry]

Here is the setup:



Table 1 : Each record has 2 stock symbols as follows:

Symbol 1 Symbol 2
IBM GE
AAPL GOOG
GOOG GE

Table 2 : Each record consists of 1 stock symbol along with a yield as follows:

Symbol Yield
IBM 3.0%
GOOG 2.0%
AAPL 3.5%
GE 3.75%
PG 4.0%
KFT 3.2%

Please notice that Table 2 has more names than Table 1. I am looking for the final result to look like:

Symbol 1 Yield Symbol 2 Yield
IBM 3.0% GE 3.75%
AAPL 3.5% GOOG 2.0%
GOOG 2.0% GE 3.75%

In the query I have built, I have:

Field 1 = Symbol 1 from Table 1
Field 2 = DLookUp([Table 2]![Symbol],"Table 2",[Table 2]![Symbol]=[Table 1]![Symbol 1])
Field 3 = Symbol 2 from Table 1
Field 4 = DLookUp([Table 2]![Symbol],"Table 2",[Table 2]![Symbol]=[Table 1]![Symbol 2])

I have attempted several joins, but nothing seems to work because of the 2 fields on the same record from Table 1. Not joining at all, of course, gives me all of the records with plenty of nulls. All I am looking to replicate is the vlookup function from Excel. I feel like the solution is probably very simple, however I have found it difficult to describe the problem in a Google search. Thus my question in this forum.

Any help would be greatly appreciated.

Thanks

[June7]

Try:

Field 2 = DLookUp("[Yield]","Table 2","[Symbol]='" & [Symbol 1] & "'")
Field 4 = DLookUp("[Yield]","Table 2","[Symbol]='" & [Symbol 2] & "'")

Advise no spaces, special character/punctuation (underscore is exception) in names.

[waynemurry]

That was a huge help. It worked. Thanks.