One to Many Criteria Check

[railfan1975]

I am putting a query together using fields from two tables. In Table 1, I have Field A that I want to check against Fields C and D in Table 2. If Table 1.Field A = Table 2.Field C or Table 1.Field A = Table 2.Field D I want the value in Table2.Field E. If both matches fail, I want the query to return "XXXX".

What would be the best way to do this?

Thanks!

 

[Andrew Fergus]

Hi

How about something like this:

SELECT
   Table1.FieldA, 
   IIf(DCount("[FieldC]","Table2","[FieldC] = '" & [FieldA] & "'")>0,
     DLookUp("[FieldE]","Table2","[FieldC] = '" & [FieldA] & "'"),
     IIf(DCount("[FieldD]","Table2","[FieldD] = '" & [FieldA] & "'")>0,
       DLookUp("[FieldE]","Table2","[FieldD] = '" & [FieldA] & "'"),
       "XXXX"))
   AS ReturnField
FROM Table1;

This is the SQL view of the query - you can get into that when in the query design screen by clicking on menu option View > SQL View. This could also be done using subqueries but I elected to use the DCount and DLookup functions which are simpler to understand if you are just starting out with SQL. If you used example table and field names then you will need to use your actual table and field names in place of the ones I used.

HTH, Andrew

 

[giacomo]

Here's another solution using outer joins...

SELECT 
[Table 1].FieldA 
, nz(A.[Field E],nz(B.[Field E],"XXXX")) as X
FROM ([Table 1] LEFT JOIN [Table2] as A ON [Table 1].FieldA = A.[Field C]) LEFT JOIN [Table2] as B ON [Table 1].FieldA = B.[Field D];

hth,
Giacomo