0

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))))))));
YowE3K
  • 23,852
  • 7
  • 26
  • 40
bernando_vialli
  • 947
  • 4
  • 12
  • 27
  • I think you need to [UNPIVOT](https://stackoverflow.com/questions/7255423/how-to-simulate-unpivot-in-access-2010) your Table2, and then join on the result. – Andre Aug 03 '17 at 22:29

2 Answers2

1

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, ...
Ross Presser
  • 6,027
  • 1
  • 34
  • 66
  • I just tried what you are doing and still have the same problem, am I missing something: SELECT DISTINCT Product.Category, Analytics.Unique FROM Product LEFT JOIN Analytics ON Analytics.Search = IIF(Product.Category Is Not Null, Product.Category, IIF(Product.Field4 Is Not Null, Product.Field4, IIF(Product.Field5 Is Not Null, Product.Field5))); – bernando_vialli Aug 03 '17 at 18:29
  • 1
    Are you sure your fields are really NULL and not just empty strings? – Ross Presser Aug 03 '17 at 18:33
  • yea I think that could be a problem, what do I do if they are empty strings? – bernando_vialli Aug 03 '17 at 18:36
  • You can check for both: `IIF(Product.Category Is Not Null and Product.Category <> ""` – Ross Presser Aug 03 '17 at 21:30
  • Thanks! I got this to work but now I found out I need to update what I have to do in this assignment and I can't get the update to work so I will create a new question now, reference this one as well. – bernando_vialli Aug 07 '17 at 18:36
  • @ Ross Presser, would you please be able to check out my other question, I really like how you answered this one so I think you do a very good job answering questions in a coherent manner, Thank you!: https://stackoverflow.com/questions/45553712/selecting-partial-string-matches-full-words-only – bernando_vialli Aug 07 '17 at 19:18
0
select table2.search, table1.unique
from table1 t 
inner join table2 t2 on t2.name = t.search
inner join table2 t3 on t3.field1 = t.search
inner join table2 t4 on t4.field2 = t.search
inner join table2 t5 on t5.field3 = t.search
Brandon Minnick
  • 13,342
  • 15
  • 65
  • 123
guru008
  • 119
  • 1
  • 2
  • This won't work at all. You've got 4 aliases - t2, t3, t4, t5 - and none of them are table2. The select clause won't be able to find table2.search – Ross Presser Aug 03 '17 at 21:29