CODEWITHSUNDEEP
databasems-access
pedram
pedram

Reputation: 3087

Is it possible to perform a join in Access on a second column if the first is blank?

I have this ugly source data with two columns, let's call them EmpID and SomeCode. Generally EmpID maps to the EmployeeListing table. But sometimes, people are entering the Employee IDs in the SomeCode field.

The person previously running this report in Excel 'solved' this problem by performing multiple vlookups with if statements, as well as running some manual checks to ensure results were accurate. As I'm moving these files to Access I am not sure how best to handle this scenario.

Ideally, I'm hoping to tell my queries to do a Left Join on SomeCode if EmpID is null, otherwise Left Join on EmpID

Unfortunately, there's no way for me to force validation or anything of the sort in the source data.

Here's the full SQL query I'm working on:

SELECT DDATransMaster.Fulfillment, 

DDATransMaster.ConfirmationNumber, 
DDATransMaster.PromotionCode,
DDATransMaster.DirectSellerNumber, 
NZ([DDATransMaster]![DirectSellerNumber],[DDATransMaster]![PromotionCode]) AS EmpJoin,
EmployeeLookup.ID AS EmpLookup,

FROM FROM DDATransMaster

LEFT JOIN EmployeeLookup ON NZ([DDATransMaster]![DirectSellerNumber],[DDATransMaster]![PromotionCode])  = EmployeeLookup.[Employee #])

Upvotes: 2

Views: 106

Answers (3)

HansUp
HansUp

Reputation: 97101

You can create a query like this:

SELECT
    IIf(EmpID Is Null, SomeCode, EmpID) AS join_field,
    field2,
    etc
FROM YourTable

Or if the query will always be used within an Access session, Nz is more concise.

SELECT
    Nz(EmpID, SomeCode) AS join_field,
    field2,
    etc
FROM YourTable

When you join that query to your other table, the Access query designer can represent the join between join_field and some matching field in the other table. If you were to attempt the IIf or Nz as part of the join's ON clause, the query designer can't display the join correctly in Design View --- it could still work, but may not be as convenient if you're new to Access SQL.

See whether this SQL gives you what you want.

SELECT
    dda.Fulfillment, 
    dda.ConfirmationNumber, 
    dda.PromotionCode,
    dda.DirectSellerNumber, 
    NZ(dda.DirectSellerNumber,dda.PromotionCode) AS EmpJoin,
    el.ID AS EmpLookup
FROM
    DDATransMaster AS dda
    LEFT JOIN EmployeeLookup AS el
    ON NZ(dda.DirectSellerNumber,dda.PromotionCode) = el.[Employee #])

But I would use the Nz part in a subquery.

SELECT
    sub.Fulfillment, 
    sub.ConfirmationNumber, 
    sub.PromotionCode,
    sub.DirectSellerNumber, 
    sub.EmpJoin,
    el.ID AS EmpLookup
FROM
    (
        SELECT
            Fulfillment, 
            ConfirmationNumber, 
            PromotionCode,
            DirectSellerNumber, 
            NZ(DirectSellerNumber,PromotionCode) AS EmpJoin
        FROM DDATransMaster
    ) AS sub
    LEFT JOIN EmployeeLookup AS el
    ON sub.EmpJoin = el.[Employee #])

Upvotes: 3

SoftwareCarpenter
SoftwareCarpenter

Reputation: 3923

You Could use a Union:

SELECT DDATransMaster.Fulfillment, 
DDATransMaster.ConfirmationNumber, 
DDATransMaster.PromotionCode,
DDATransMaster.DirectSellerNumber, 
EmployeeLookup.ID AS EmpLookup
FROM DDATransMaster
LEFT JOIN EmployeeLookup ON 
DDATransMaster.DirectSellerNumber = EmployeeLookup.[Employee #]
where DDATransMaster.DirectSellerNumber IS NOT NULL

Union

SELECT DDATransMaster.Fulfillment, 
DDATransMaster.ConfirmationNumber, 
DDATransMaster.PromotionCode,
DDATransMaster.DirectSellerNumber, 
EmployeeLookup.ID AS EmpLookup
FROM DDATransMaster
LEFT JOIN EmployeeLookup ON 
DDATransMaster.PromotionCode = EmployeeLookup.[Employee #]
where DDATransMaster.DirectSellerNumber IS NULL;

Upvotes: 1

Chris
Chris

Reputation: 2952

What about:

LEFT JOIN EmployeeListing ON NZ(EmpID, SomeCode)

as your join, nz() uses the second parameter if the first is null, I'm not 100% sure this sort of join works in access. Worth 20 seconds to try though.

Hope it works.

Upvotes: 1

Related Questions