结合条件的多个字段 [英] Combine multiple fields with condition

问题描述

我有多个MS Access字段，我需要将大多数字段与条件组合在一起.我仍然是复杂SQL查询的初学者，因此将以下伪代码转换为现实的局限性:

I have multiple MS Access fields and I need to combine most of them with a condition. I'm still a beginner with complicated SQL queries thus the limitation to translate the following pseudocode into reality:

SELECT field_1, field_2, field_3
CREATE new_field

// these would be WHERE-queries
if field_1 & field_2 are "missing", "unknown" or empty
    then new_field = field_3 

else if field_2 & field_3 are "missing", "unknown" or empty
    then new_field = field_1

else if field_1 & field_3 are "missing", "unknown" or empty
    then new_field = field_2  

new_field基本上将包含组合的field_1，field_2，field_3 ... field_n的值.假设这些n字段中只有一个具有合法值.

The new_field will basically contain the values of combined field_1, field_2, field_3 ... field_n. Assume that only one of these n-fields have a legit value.

使用简单的查询在MS Access中可以实现吗?

Is this achievable in MS Access using a simple query?

推荐答案

如果要在Access会话中运行查询，则可以使用Nz.这将返回包含非null值的字段的值...

If you will be running the query within an Access session you can use Nz. This will return the value of the field which contains a non-Null value ...

SELECT Nz(field_1, '') & Nz(field_2, '') & Nz(field_3, '') AS combined

如果您需要一个可以在Access会话外部运行的查询，则Nz不可用.请使用IIf表达式.

If you need a query which can work from outside an Access session, Nz is not available. Use an IIf expression instead.

SELECT
      IIf(field_1 Is Null, '', field_1)
    & IIf(field_2 Is Null, '', field_2)
    & IIf(field_3 Is Null, '', field_3)

这篇关于结合条件的多个字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！