查询仅从一列而不是所有列中拉入输出 [英] Query only pulling in output from one column instead of all columns

问题描述

我实际上首先尝试在excel中使用一堆嵌套的vlookups并遇到相同的错误，因此我尝试在访问中做到这一点，以解决问题，但我遇到了完全相同的问题.

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.

我要做的就是通过一个内部联接连接两个表，其中一个表中的1列和另一个表中的许多列之一(而我希望输出是(导致问题的那个))成为特定的一列.这是数据的一小部分样本，以及我尝试在查询后得到的样本.

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.

表1:

Search    unique

gloves    5000
beaker    3000
tea       1000
timer     2000

表2:

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

当我进行内部联接时，我得到:

when I do an inner join I get:

gloves 5000

而不是:

gloves 5000
tea    1000
timer  2000

因此，它仅在第一列加入，不知道为什么?这是我在Access中编写的查询:

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))))))));

推荐答案

我相信您的表达式并没有测试您认为正在测试的内容.局部表达式

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

Analytics.Search = Product.Category Is Not Null

等同于

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

换句话说，测试布尔值是否不为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, ...

这篇关于查询仅从一列而不是所有列中拉入输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！