Search code examples
sqlms-accessnullsubquery

MS Access: How to query a second table when value is not found in the first

I have a simple query to find values in a related table. In some cases, the value is null. In this case, I would like to query a second table. I looked at the NZ function but it won't allow for another query operation. I assume I am looking at a nested query or an operation like SQL COALASCE.

I want to first search tbl_bi_item_mstr

SELECT tbl_cust_fcst_demd.cust_item_nr, tbl_bi_item_mstr.bi_item_nr
FROM tbl_cust_fcst_demd LEFT JOIN tbl_bi_item_mstr ON tbl_cust_fcst_demd.cust_item_nr = tbl_bi_item_mstr.bi_item_nr;

For the null values, I would want to find them in tbl_cust_xref

SELECT tbl_cust_fcst_demd.cust_item_nr, tbl_cust_xref.bi_item_nr
FROM tbl_cust_fcst_demd INNER JOIN tbl_cust_xref ON tbl_cust_fcst_demd.cust_item_nr = tbl_cust_xref.cust_item_nr;
Solution

  • You can simply join both tables, and use Nz() between the result fields.

    SELECT d.cust_item_nr, Nz(i.bi_item_nr, x.bi_item_nr) AS bi_item_nr
FROM (tbl_cust_fcst_demd d 
LEFT JOIN tbl_bi_item_mstr i ON d.cust_item_nr = i.bi_item_nr)
INNER JOIN tbl_cust_xref x ON d.cust_item_nr = x.cust_item_nr

    For >1 JOIN, parentheses are needed.
    Using table aliases makes the SQL easier readable.