Search code examples
sqlms-accessms-access-2016

Query only pulling in output from one column instead of all columns

I actually first tried doing this in excel with a bunch of nested vlookups and encountered the same error so I tried doing it in access thinking I would resolve the problem, but I get the exact same issue.

All I am trying to do is join two tables by an inner join with 1 column in the one table and one of many columns in the other table (and I want the output to be (the one that is causing my issue) to be one particular column. Here is a small sample of the data and what I tried to get followed by my query.

Table 1:

Search    unique

gloves    5000
beaker    3000
tea       1000
timer     2000

Table 2:

Name   Field1   Field 2    Field3 ....
gloves hello    goodbye
Time   timer    clock
hi     tea

when I do an inner join I get:

gloves 5000

instead of getting:

gloves 5000
tea    1000
timer  2000

Hence it is only joining it on the 1st column, no idea why? Here is the Query I wrote in Access:

SELECT DISTINCT Product.Category, Analytics.Unique
FROM Product INNER JOIN Analytics ON IIF(Analytics.Search = Product.Category 
Is Not Null,Analytics.Search = Product.Category, IIF(Analytics.Search = 
Product.Field4 Is Not Null, Analytics.Search = Product.Field4, 
IIF(Analytics.Search = Product.Field5 Is Not Null, Analytics.Search = 
Product.Field5, IIF(Analytics.Search = Product.Field6 Is Not Null, 
Analytics.Search = Product.Field6, IIF(Analytics.Search = Product.Field7 Is 
Not Null, Analytics.Search = Product.Field7, IIF(Analytics.Search = 
Product.Field8 Is Not Null, Analytics.Search = Product.Field8,  
IIF(Analytics.Search = Product.Field9 Is Not Null, Analytics.Search = 
Product.Field9, IIF(Analytics.Search = Product.Field10 Is Not Null, 
Analytics.Search = Product.Field10))))))));
Solution

  • I believe that your expressions are not testing what you think they are testing. The partial expression 

    Analytics.Search = Product.Category Is Not Null

    is equivalent to

    (Analytics.Search = Product.Category) Is Not Null

    In other words, testing whether a boolean value is not null. And boolean values never are null.

    You probably want something more like this:

    ... ON Analytics.Search = IIF(Product.Category Is Not Null, Product.Category, 
                          IIF(Product.Field4 Is Not Null, Product.Field4, ...