Microsoft Access，双数据输入和违反规范化规则 [英] Microsoft Access, Double Data Entry and breaking the Normalization rule

问题描述

大家好，


我正在寻找一些关于是否应该打破

规范化规则的建议。通常情况下，我不会考虑它，但这似乎是一个特例。


我创建了一个结果数据库。用于存储响应数据

来自纵向健康研究的测量/问卷调查。它基本上来自Duane Hookom的调查数据库（感谢
Duane !!!），并添加了许多修改以满足我实验室的需要。

其中一个补充是能够进行双数据输入。在

中为了验证数据（按照授权的要求）。


所有数据都输入到tbl_DateE中，并带有以下5个

关键字段：[DtaPartID]（参与者ID），[DtaQstnE]（评估

编号），[DtaSrvID]（调查ID），[DtaEntry]（第1对第2）条目），

[DtaQstnQ]（问题编号）。还有一个字段[DtaRspns]

包含参与者的实际响应。


我写了VBA代码，将第一个条目与第二个条目进行比较

条目，输出表格的差异。这些错误很容易打印出来，以便对硬拷贝进行手工检查。然而，我没有想到自动识别错误的方式，因为数据输入第二次让我非常高兴。最好的可能是

使用DAO.recordset和FindFirst命令比较第二个到第二个

。如果存在差异，则此代码将更新flag

字段。然后可以过滤和纠正这些错误。


另一方面，似乎可能更容易去标准化

表稍微有一个[DtaRspn1]和一个[DtaRspn2]字段。这些

字段可以比上面建议的FindFirst

方法更容易，也可能更快。


您可能有任何想法非常感谢。我犹豫不决

打破规范化规则，因为过去这总是来了

回来困扰我。 。 。 。


谢谢，

Paul

Hello everyone,

I''m looking for some advice on whether I should break the
normalization rule. Normally, I would not consider it, but this seems
to be a special case.

I have created an "Outcomes Database" used to store response data
from measures/ questionnaires for a longitudinal health study. It is
essentially derived from Duane Hookom''s Survey Database (thanks
Duane!!!), with many modifications added to fit the needs of my lab.
One of the additions, is the ability to do "Double Data Entry" in
order to verify the data (as required by the grant).

All the data is entered into the "tbl_DateE", with the following 5
Key fields: [DtaPartID] (Participant ID), [DtaQstnE] (evaluation
number), [DtaSrvID] (Survey ID), [DtaEntry] (1st versus 2nd entry),
[DtaQstnQ] (Question Number). There is one other field [DtaRspns] that
contains the participant actual response.

I''ve written VBA code that compares the first entry to the second
entry, outputting discrepancies to a table. These errors can be easily
printed in order to hand-check against the hard-copy. However, none of
the ways I can think of to do automatic identification of errors as
the data is being 2nd entered make me very happy. The best might be
using a DAO.recordset and the FindFirst command to compare the 2nd to
the 1st. If there is a discrepancy this code would update a "flag"
field. Then these errors could be filtered and corrected.

On the other hand, it seems that it might be easier to "de-Normalize"
the table slightly to have a [DtaRspn1] and a [DtaRspn2] field. These
fields could be compared easier and possibly faster than the FindFirst
method suggested above.

Any thoughts you might have would be much appreciated. I''m hesitant
to break the normalization rule, as in the past this has always come
back to haunt me. . . .

Thanks,
Paul

推荐答案

On 5月29日下午3:34，hippome ... @ googlemail.com

< hippome ... @ googlemail.comwrote：

On May 29, 3:34 pm, "[e-mail]"
<[e-mail]:

大家好，


我正在寻找一些关于是否应该打破

规范化规则的建议。通常情况下，我不会考虑它，但这似乎是一个特例。


我创建了一个结果数据库。用于存储响应数据

来自纵向健康研究的测量/问卷调查。它基本上来自Duane Hookom的调查数据库（感谢
Duane !!!），并添加了许多修改以满足我实验室的需要。

其中一个补充是能够进行双数据输入。在

中为了验证数据（按照授权的要求）。


所有数据都输入到tbl_DateE中，并带有以下5个

关键字段：[DtaPartID]（参与者ID），[DtaQstnE]（评估

编号），[DtaSrvID]（调查ID），[DtaEntry]（第1对第2）条目），

[DtaQstnQ]（问题编号）。还有一个字段[DtaRspns]

包含参与者的实际响应。


我写了VBA代码，将第一个条目与第二个条目进行比较

条目，输出表格的差异。这些错误很容易打印出来，以便对硬拷贝进行手工检查。然而，我没有想到自动识别错误的方式，因为数据输入第二次让我非常高兴。最好的可能是

使用DAO.recordset和FindFirst命令比较第二个到第二个

。如果存在差异，则此代码将更新flag

字段。然后可以过滤和纠正这些错误。


另一方面，似乎可能更容易去标准化

表稍微有一个[DtaRspn1]和一个[DtaRspn2]字段。这些

字段可以比上面建议的FindFirst

方法更容易，也可能更快。


您可能有任何想法非常感谢。我犹豫不决

打破规范化规则，因为过去这总是来了

回来困扰我。 。 。 。


谢谢，

Paul

Hello everyone,

I''m looking for some advice on whether I should break the
normalization rule. Normally, I would not consider it, but this seems
to be a special case.

I have created an "Outcomes Database" used to store response data
from measures/ questionnaires for a longitudinal health study. It is
essentially derived from Duane Hookom''s Survey Database (thanks
Duane!!!), with many modifications added to fit the needs of my lab.
One of the additions, is the ability to do "Double Data Entry" in
order to verify the data (as required by the grant).

All the data is entered into the "tbl_DateE", with the following 5
Key fields: [DtaPartID] (Participant ID), [DtaQstnE] (evaluation
number), [DtaSrvID] (Survey ID), [DtaEntry] (1st versus 2nd entry),
[DtaQstnQ] (Question Number). There is one other field [DtaRspns] that
contains the participant actual response.

I''ve written VBA code that compares the first entry to the second
entry, outputting discrepancies to a table. These errors can be easily
printed in order to hand-check against the hard-copy. However, none of
the ways I can think of to do automatic identification of errors as
the data is being 2nd entered make me very happy. The best might be
using a DAO.recordset and the FindFirst command to compare the 2nd to
the 1st. If there is a discrepancy this code would update a "flag"
field. Then these errors could be filtered and corrected.

On the other hand, it seems that it might be easier to "de-Normalize"
the table slightly to have a [DtaRspn1] and a [DtaRspn2] field. These
fields could be compared easier and possibly faster than the FindFirst
method suggested above.

Any thoughts you might have would be much appreciated. I''m hesitant
to break the normalization rule, as in the past this has always come
back to haunt me. . . .

Thanks,
Paul

保罗，


听起来像是反规范化的好时机。在我看来，这些是单独的

数据点，它们是同一个真实世界对象的一部分。


规范化非常重要，而且它是'很好，你的倾向是正常化，但在很多情况下，严格的规范化将会导致在现实世界中表现不佳的情况，以及

你的工作就是让你的数据库反映现实世界，而不是让你的数据库与一组规则相匹配。规则只存在，因为它们通常有助于进行建模。在这样的情况下，

对标准化提出质疑很好。


JeremyNYC

Paul,

Sounds like a good time to denormalize. In my eyes, these are separate
data points that are part of the same real-world object.

Normalization is really important, and it''s good that your tendency is
to normalize, but there are many cases where strict normalization
would lead to a poor representation of what''s in the real world, and
your job is to make your database reflect the real world, not to make
your database match up to a set of rules. The rules only exist because
they are usually helpful in doing that modeling. In cases such as this
it''s good to question normalization.

JeremyNYC

5月29日下午3:45，absolutejunkforjer ... @ gmail.com

< absolutejunkforjer ... @ gmail.comwrote：

On May 29, 3:45 pm, "[e-mail]"
<[e-mail]:

5月29日下午3:34，hippome ... @ googlemail.com


< hippome ... @ googlemail。 comwrote：

On May 29, 3:34 pm, "[e-mail]"

<[e-mail]:

大家好，

Hello everyone,

我正在寻找一些建议我是否应该打破

规范化规则。通常情况下，我不会考虑它，但这似乎是一个特例。

。

I''m looking for some advice on whether I should break the
normalization rule. Normally, I would not consider it, but this seems
to be a special case.

我创建了一个结果数据库用于存储响应数据

来自纵向健康研究的测量/问卷调查。它基本上来自Duane Hookom的调查数据库（感谢
Duane !!!），并添加了许多修改以满足我实验室的需要。

其中一个补充是能够进行双数据输入。在

命令验证数据（根据授权要求）。

I have created an "Outcomes Database" used to store response data
from measures/ questionnaires for a longitudinal health study. It is
essentially derived from Duane Hookom''s Survey Database (thanks
Duane!!!), with many modifications added to fit the needs of my lab.
One of the additions, is the ability to do "Double Data Entry" in
order to verify the data (as required by the grant).

所有数据都输入到tbl_DateE中，带有以下5

键字段：[DtaPartID] （参与者ID），[DtaQstnE]（评估

编号），[DtaSrvID]（调查ID），[DtaEntry]（第1次与第2次参赛），

[DtaQstnQ] （问题编号）。还有一个字段[DtaRspns]

包含参与者的实际响应。

All the data is entered into the "tbl_DateE", with the following 5
Key fields: [DtaPartID] (Participant ID), [DtaQstnE] (evaluation
number), [DtaSrvID] (Survey ID), [DtaEntry] (1st versus 2nd entry),
[DtaQstnQ] (Question Number). There is one other field [DtaRspns] that
contains the participant actual response.

我写了VBA代码，将第一个条目与第二个

条目进行比较，输出与表格的差异。这些错误很容易打印出来，以便对硬拷贝进行手工检查。然而，我没有想到自动识别错误的方式，因为数据输入第二次让我非常高兴。最好的可能是

使用DAO.recordset和FindFirst命令比较第二个到第二个

。如果存在差异，则此代码将更新flag

字段。然后可以过滤和纠正这些错误。

I''ve written VBA code that compares the first entry to the second
entry, outputting discrepancies to a table. These errors can be easily
printed in order to hand-check against the hard-copy. However, none of
the ways I can think of to do automatic identification of errors as
the data is being 2nd entered make me very happy. The best might be
using a DAO.recordset and the FindFirst command to compare the 2nd to
the 1st. If there is a discrepancy this code would update a "flag"
field. Then these errors could be filtered and corrected.

另一方面，似乎可能更容易去标准化

表略有一个[DtaRspn1]和一个[DtaRspn2]字段。这些

字段可以比上面建议的FindFirst

方法更容易，也可能更快。

On the other hand, it seems that it might be easier to "de-Normalize"
the table slightly to have a [DtaRspn1] and a [DtaRspn2] field. These
fields could be compared easier and possibly faster than the FindFirst
method suggested above.

您可能会有任何想法，我将不胜感激。我犹豫不决

打破规范化规则，因为过去这总是来了

回来困扰我。 。 。 。

Any thoughts you might have would be much appreciated. I''m hesitant
to break the normalization rule, as in the past this has always come
back to haunt me. . . .

谢谢，

Paul

Thanks,
Paul

Paul，


听起来像是反规范化的好时机。在我看来，这些是单独的

数据点，它们是同一个真实世界对象的一部分。


规范化非常重要，而且它是'很好，你的倾向是正常化，但在很多情况下，严格的规范化将会导致在现实世界中表现不佳的情况，以及

你的工作就是让你的数据库反映现实世界，而不是让你的数据库与一组规则相匹配。规则只存在，因为它们通常有助于进行建模。在像这样的情况下，你可以对标准化提出质疑。


JeremyNYC

Paul,

Sounds like a good time to denormalize. In my eyes, these are separate
data points that are part of the same real-world object.

Normalization is really important, and it''s good that your tendency is
to normalize, but there are many cases where strict normalization
would lead to a poor representation of what''s in the real world, and
your job is to make your database reflect the real world, not to make
your database match up to a set of rules. The rules only exist because
they are usually helpful in doing that modeling. In cases such as this
it''s good to question normalization.

JeremyNYC

谢谢杰里米。我绝对倾向于在这个

案例中取消正常化。

Thanks Jeremy. I''m definitely leaning towards de-normalization in this
case.

" hi ****** **@googlemail.com" < hi ******** @ googlemail.comwrote：

"hi********@googlemail.com" <hi********@googlemail.comwrote:

另一方面，似乎可能更容易去标准化 ;表稍微有一个[DtaRspn1]和一个[DtaRspn2]字段。

On the other hand, it seems that it might be easier to "de-Normalize"
the table slightly to have a [DtaRspn1] and a [DtaRspn2] field.

这不是非规范化。您有相同的数据，但输入了两次。这是完全合法的。


在概念或概述中，这类似于存储成本和价格

物品销售时的物品。交易完成后，所有成本和价格都会改变时间

。


Tony

-

Tony Toews，Microsoft Access MVP

请仅在新闻组中回复，以便其他人可以

阅读整个消息主题。

Microsoft Access Links，Hints，Tips&会计系统
http://www.granite.ab.ca /accsmstr.htm

Tony的Microsoft Access博客 - http://msmvps.com/blogs/access/

这篇关于Microsoft Access，双数据输入和违反规范化规则的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！