# One to Many Criteria Check

#### railfan1975

##### New Member
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!

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Andrew Fergus

##### MrExcel MVP
Hi

How about something like this:

Code:
``````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

##### Well-known Member
Here's another solution using outer joins...
Code:
``````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

Replies
1
Views
64
Replies
6
Views
188
Replies
7
Views
157
Replies
3
Views
98
Replies
1
Views
85

1,191,614
Messages
5,987,680
Members
440,104
Latest member
thigarette

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back