The Query Lost My Records!

jsy165

来源：http://allenbrowne.com/casu-02.html

文章标题：The Query Lost My Records!

Nulls in Criteria

You wish to mail two different messages - one to your WA friends and another to those in other states. You create two queries: one where the Criteria line under State reads WA , and a second query where the Criteria line reads Not "WA" . You could think this would take care of all cases. It doesn't!

The problem is the way Access and other true relational databases handle Nulls. If the entry under State has been left completely blank, the record will not show up in either of the above queries. You must specifically ask Access to check for Nulls, by using a Criteria such as Is Null Or Not "WA". Whenever you enter criteria for a query, think about the possibilities of Nulls.

In some cases, you will want to prevent Nulls occurring. In Access 2 or later, open the table in Design View, click on the field, and in the properties at the bottom of the screen, set Required to "Yes".

Nulls in Joins

The same "Gotcha!" occurs when you have two tables joined on a field that can contain a Null. For example, a Customer table might be joined to an Invoice table on a CustomerID field. To cover "Cash Sales", you allow invoices to be entered with a blank CustomerID.

Now you create a query with both the Customer and Invoice tables, as the basis for a report showing all sales. If you are observant, you notice that your Cash Sales are missing, so the report has incorrect totals!

The solution is very simple. In the Query Design grid, double-click the line that joins the two queries, and Access will pop up a dialog box asking what type of JOIN you want. Select the appropriate OUTER JOIN, and all the "Cash Sales" will reappear in your query and in the report.

译文：

查询丢失了我的记录

标准的为空

你希望寄两封不同的信件，一封到美国的华盛顿州，另一封到其他州。你就要建立两个查询：一个是标准栏读到为华盛顿州的，而另一个则是读取不是为华盛顿州的。你能够想象所有的工程是需要小心保护好的。那是不必要的。

问题是这种方式ACCESS和其他正确关系的数据库句柄为空。如果登入州下面已经完成的空白处，记录不会出现在该问题的上面的。你必须明确的告诉ACCESS要去检查是否为空，使用Is Null Or Not "Waf”标准去判断。无论什么时候你都要考虑是否为空，用特定的标准去判断它。

在一些情况下，你需要出现为空的事件。在ACCESS 2或以后的版本，打开表到设计模式，点击该区域，然后在控件的底部，设置要求为“是”。

联合为空

同样的"Gotcha!"事件，当你要将两个表联合在一个区域的时候就会出现为空。例如一个客户表就可能要加入货物表在客户的ID里，要覆盖“售出的货”，你必须允许客户ID被引用。

现在你建立两个表一个客户表及一个货物表，像报表样展示所有售出货物。如果你养成了习惯，你提醒，你所有的销售数据将丢失，将会导致报表统计错误。

解决方案非常简单，在查询的设计模式中的“双击事件”里加入两个要求，然后ACCESS就会突然出现一个你要加如什么类型对话框。选择适当的外部命令加入，这样你的销售数据栏就可以出现在你的查询或是报表里了。

翻译：飞天业