Microsoft Access, Double Data Entry and breaking the Normalization rule

[hippomedon@googlemail.com]

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

[absolutejunkforjeremy@gmail.com]

Re: Microsoft Access, Double Data Entry and breaking the Normalization rule

On May 29, 3:34 pm, "hippome...@goo glemail.com"
<hippome...@goo glemail.comwrot e:

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

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

[hippomedon@googlemail.com]

Re: Microsoft Access, Double Data Entry and breaking the Normalization rule

On May 29, 3:45 pm, "absolutejunkfo rjer...@gmail.c om"
<absolutejunkfo rjer...@gmail.c omwrote:

On May 29, 3:34 pm, "hippome...@goo glemail.com"
>
>
>
<hippome...@goo glemail.comwrot e:

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

>
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.

[Tony Toews [MVP]]

Re: Microsoft Access, Double Data Entry and breaking the Normalization rule

"hippomedon@goo glemail.com" <hippomedon@goo glemail.comwrot e:

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.

This is not denormalizing. You have the same data but entered twice. It's
perfectly legitimate to do this.

At the concept or overview lefel this is similar to storing the cost and price of an
item when you sell the item. After all the cost and price could change moments
after the transaction is entered.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at

http://www.granite.ab.ca/accsmstr.htm

Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[hippomedon@googlemail.com]

Re: Microsoft Access, Double Data Entry and breaking the Normalization rule

On May 29, 4:23 pm, "Tony Toews [MVP]" <tto...@teluspl anet.netwrote:

"hippome...@goo glemail.com" <hippome...@goo glemail.comwrot e:

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.

>
This is not denormalizing. You have the same data but entered twice. It's
perfectly legitimate to do this.
>
At the concept or overview lefel this is similar to storing the cost and price of an
item when you sell the item. After all the cost and price could change moments
after the transaction is entered.
>
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems athttp://www.granite.ab. ca/accsmstr.htm
Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/

Thanks for the clarification Tony. I'm going ahead with it. So far
it's working great!

[David W. Fenton]

Re: Microsoft Access, Double Data Entry and breaking the Normalization rule

"hippomedon@goo glemail.com" <hippomedon@goo glemail.comwrot e in
news:1180467269 .192782.263950@ q69g2000hsb.goo glegroups.com:

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

Do you have to store the results of the double entry? If not, then
just load the existing record as a recordset and use an unbound form
to check the 2nd entry in the unbound form against the stored entry.
You could even use a form bound to a single record and just not bind
the controls.

Obviously, if you have to *store* the 2nd entry, not applicable,
though if you want to check at data entry time, this would work even
with a bound form for the new data (using an in-memory recordset
with the first version of the data).

If you want to check for discrepancies in a batch, I recommend doing
it by generating a series of SQL statements, one field for each,
that checks the values in the two tables against each other, and if
something is found, writes to a table what the discrepancy is.

Both of these ideas are something I've already implemented in other
apps.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

Re: Microsoft Access, Double Data Entry and breaking the Normalization rule

"absolutejunkfo rjeremy@gmail.c om" <absolutejunkfo rjeremy@gmail.c om>
wrote in news:1180467945 .559821.13580@g 4g2000hsf.googl egroups.com:

Sounds like a good time to denormalize.

If both versions of the data need to be stored, I see no reason why
it should be considered denormalization .

I don't think I'd consider doing both versions in one record,
though. I think it's better to either have two tables with the same
structure, or use a "narrow" table for the second entry, where you
record in each field the value entered and which field it is, and
which main record it applies to. Or, if the 2nd entry doesn't have
to be recorded, you could record in this table only the data entry
discrepancies.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

Re: Microsoft Access, Double Data Entry and breaking the Normalization rule

"Tony Toews [MVP]" <ttoews@teluspl anet.netwrote in
news:up2p53p5kd 7dlnuckscti79kl vaula90ki@4ax.c om:

"hippomedon@goo glemail.com" <hippomedon@goo glemail.comwrot e:
>

>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.

>
This is not denormalizing. You have the same data but entered
twice. It's perfectly legitimate to do this.
>
At the concept or overview lefel this is similar to storing the
cost and price of an item when you sell the item. After all the
cost and price could change moments after the transaction is
entered.

Yes, but there are different ways to store the data:

1. double the fields in a single record.

2. have two records in a single table, with identical structure.

3. have two separate tables with identical structure.

4. have a main table and then a narrow side table that records only
the discrepancies, one field per record.

The worst of all seems to me to be the 1st choice, which is, I
believe, what the OP is considering.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[hippomedon@googlemail.com]

Re: Microsoft Access, Double Data Entry and breaking the Normalization rule

On May 29, 8:42 pm, "David W. Fenton" <XXXuse...@dfen ton.com.invalid >
wrote:

"Tony Toews [MVP]" <tto...@teluspl anet.netwrote innews:up2p53p5 kd7dlnuckscti79 klvaula90ki@4ax .com:
>

"hippome...@goo glemail.com" <hippome...@goo glemail.comwrot e:

>

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.

>

This is not denormalizing. You have the same data but entered
twice. It's perfectly legitimate to do this.

>

At the concept or overview lefel this is similar to storing the
cost and price of an item when you sell the item. After all the
cost and price could change moments after the transaction is
entered.

>
Yes, but there are different ways to store the data:
>
1. double the fields in a single record.
>
2. have two records in a single table, with identical structure.
>
3. have two separate tables with identical structure.
>
4. have a main table and then a narrow side table that records only
the discrepancies, one field per record.
>
The worst of all seems to me to be the 1st choice, which is, I
believe, what the OP is considering.
>
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Thanks David. Unfortunately, I have to store the 2nd entry so the
unbound form method would not work.

Currently the db is set-up with option 2 (two records in same table).
This works very well for validation purposes, when the validation is
done after the entry. The VBA code that I developed easily compares
Entry 1 to Entry 2 and generates an output. However, for instantaneous
validation, it does not work as well. This is the main reason I'm
considering switching to option 1. I think it would allow for an
easier and more instantaneous comparison of entry 2 to entry 1. It
should actually only add 2 fields to each record (one for the
response, and one to record the time of entry).

Why do you think that this option is the worst? The only potential
disadvantage that I can foresee is that if a "record" is deleted
accidentally, it would be easy to miss. However, the database is well
secured from the end-users, and I have queries that check against
missing records prior to exporting to SPSS (since every participant is
supposed to have the same number of questions).

Your further thoughts would be appreciated.

Thanks,
Paul

PS. I've used two tables in the past, but found that having one table
generally worked better (validations were faster, querying easier,
etc).

[David W. Fenton]

Re: Microsoft Access, Double Data Entry and breaking the Normalization rule

"hippomedon@goo glemail.com" <hippomedon@goo glemail.comwrot e in
news:1180540025 .677581.252670@ o5g2000hsb.goog legroups.com:

Currently the db is set-up with option 2 (two records in same
table).
This works very well for validation purposes, when the validation
is done after the entry. The VBA code that I developed easily
compares Entry 1 to Entry 2 and generates an output. However, for
instantaneous validation, it does not work as well. This is the
main reason I'm considering switching to option 1. I think it
would allow for an easier and more instantaneous comparison of
entry 2 to entry 1. It should actually only add 2 fields to each
record (one for the response, and one to record the time of
entry).

I don't see why you can't load the other record in a recordset and
compare the values field by field when they are entered into the
other table. That would be just as easy as comparing to a different
field. You'd do it something like this in the BeforeUpdate of the
control:

If Nz(Me!ControlNa me) <Nz(rs(Me!Contr olName.ControlS ource)) Then
...

You could also write a function that uses Screen.ActiveCo ntrol to do
it, which makes it easy to assign to all the controls (in the above,
replace Me!ControlName with Screen.ActiveCo ntrol).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[DavidB]

Re: Microsoft Access, Double Data Entry and breaking the Normalization rule

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 have an aside question here. Noting the table structure above, why
is it that people abbreviate field name to the point of
nonrecognition? Take for example the field DtaSrvID in this table.
Why not name it DataSurveyID which is (I ass-u-me) the data element it
contains? It will make going back into the code at a lated point MUCH
easier. I code and support a large number of databases. Some of them
written by other folks who use these abbreviated naming conventions.
There are many times it takes me a lot of time to figure out what the
data elements even are because of this. The fact that the person
asking the original question had to add an explanation of each field
next to the field name kind of makes my point. Is there any actual
advantage to using the abbreviated field names or is it just a throw
back to the early days of geekdom where space was limited akin to the
2 digit year? (Paul, I am not kicking your cat by asking this. Just
something that has been bugging me for a while and figured I would
ask.)

[hippomedon@googlemail.com]

Re: Microsoft Access, Double Data Entry and breaking the Normalization rule

On May 30, 2:02 pm, DavidB <j...@yahoo.com wrote:

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 have an aside question here. Noting the table structure above, why
is it that people abbreviate field name to the point of
nonrecognition? Take for example the field DtaSrvID in this table.
Why not name it DataSurveyID which is (I ass-u-me) the data element it
contains? It will make going back into the code at a lated point MUCH
easier. I code and support a large number of databases. Some of them
written by other folks who use these abbreviated naming conventions.
There are many times it takes me a lot of time to figure out what the
data elements even are because of this. The fact that the person
asking the original question had to add an explanation of each field
next to the field name kind of makes my point. Is there any actual
advantage to using the abbreviated field names or is it just a throw
back to the early days of geekdom where space was limited akin to the
2 digit year? (Paul, I am not kicking your cat by asking this. Just
something that has been bugging me for a while and figured I would
ask.)

Hi DavidB,
Interesting question. For me, it's always finding a balance between
having a variable name that is short and easy to type (for queries and
VBA code) and having one that is too short to be recognizable. With
something like DtaSrvID, I figure that by capitalizing every segment,
it's generally pretty recognizable. (I added clarification, just in
case). I also like have the first part of the variable refer to the
table, thus Dta references the fact that this variable comes from the
Data table.
Paul

[hippomedon@googlemail.com]

Re: Microsoft Access, Double Data Entry and breaking the Normalization rule

On May 30, 1:44 pm, "David W. Fenton" <XXXuse...@dfen ton.com.invalid >
wrote:

"hippome...@goo glemail.com" <hippome...@goo glemail.comwrot e innews:11805400 25.677581.25267 0@o5g2000hsb.go oglegroups.com:
>

Currently the db is set-up with option 2 (two records in same
table).
This works very well for validation purposes, when the validation
is done after the entry. The VBA code that I developed easily
compares Entry 1 to Entry 2 and generates an output. However, for
instantaneous validation, it does not work as well. This is the
main reason I'm considering switching to option 1. I think it
would allow for an easier and more instantaneous comparison of
entry 2 to entry 1. It should actually only add 2 fields to each
record (one for the response, and one to record the time of
entry).

>
I don't see why you can't load the other record in a recordset and
compare the values field by field when they are entered into the
other table. That would be just as easy as comparing to a different
field. You'd do it something like this in the BeforeUpdate of the
control:
>
If Nz(Me!ControlNa me) <Nz(rs(Me!Contr olName.ControlS ource)) Then
...
>
You could also write a function that uses Screen.ActiveCo ntrol to do
it, which makes it easy to assign to all the controls (in the above,
replace Me!ControlName with Screen.ActiveCo ntrol).
>
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

This certainly works, although I find that it's slightly slower than
comparing to another field. I'm curious about your concerns for having
two fields, instead of two tables.

My big problem with this method is making it aesthetically pleasing
and/or easy to use for the end user. I'll try to explain the process a
little better to show what I mean. Typically, it's fastest to enter
the 500 or so responses at each evaluation in one shot. The validation
is happening either silently in the background or done immediately
after the data has been 2nd entered (by pressing a command button, or
whatever). Then, the person doing 2nd entry is supposed to fix the
errors. Since the errors can happen in either first entry or second,
it seems easiest and most intuitive to have a 3rd "Validation " tab on
the main entry form with a subform filtered to shows only the
discrepancies. (Tabs 1 and 2 are for first and 2nd entry,
respectively). Entry 1 would be right next Entry 2, the person doing
data entry checks the hard copy and fixes whichever entry has the
mistake.

With 1 record storing both entries, the subform for the "validation
tab" is very easy to design. It's very easy to query for the
discrepancies, and easy to have an updateable dataset. With two
tables and/or two records, the "Validation tab" seems to require 2
subforms (one for each entry -- so the two entries line up
horizontally next to each other). Additionally, filtering/ querying
for the discrepancies is harder (although that may just be me).

Please let me know your concerns with this table structure! I think
the intuitively easy-to-use and slightly faster end product, justifies
this structure. I was a bit wary of increasing the number of variables
in the table, but there seems to be no real reason or disadvantage to
this!

Thanks,
Paul

[hippomedon@googlemail.com]

Re: Microsoft Access, Double Data Entry and breaking the Normalization rule

On May 30, 1:44 pm, "David W. Fenton" <XXXuse...@dfen ton.com.invalid >
wrote:

"hippome...@goo glemail.com" <hippome...@goo glemail.comwrot e innews:11805400 25.677581.25267 0@o5g2000hsb.go oglegroups.com:
>

Currently the db is set-up with option 2 (two records in same
table).
This works very well for validation purposes, when the validation
is done after the entry. The VBA code that I developed easily
compares Entry 1 to Entry 2 and generates an output. However, for
instantaneous validation, it does not work as well. This is the
main reason I'm considering switching to option 1. I think it
would allow for an easier and more instantaneous comparison of
entry 2 to entry 1. It should actually only add 2 fields to each
record (one for the response, and one to record the time of
entry).

>
I don't see why you can't load the other record in a recordset and
compare the values field by field when they are entered into the
other table. That would be just as easy as comparing to a different
field. You'd do it something like this in the BeforeUpdate of the
control:
>
If Nz(Me!ControlNa me) <Nz(rs(Me!Contr olName.ControlS ource)) Then
...
>
You could also write a function that uses Screen.ActiveCo ntrol to do
it, which makes it easy to assign to all the controls (in the above,
replace Me!ControlName with Screen.ActiveCo ntrol).
>
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

This certainly works, although I find that it's slightly slower than
comparing to another field. I'm curious about your concerns for having
two fields, instead of two tables.

My big problem with this method is making it aesthetically pleasing
and/or easy to use for the end user. I'll try to explain the process a
little better to show what I mean. Typically, it's fastest to enter
the 500 or so responses at each evaluation in one shot. The validation
is happening either silently in the background or done immediately
after the data has been 2nd entered (by pressing a command button, or
whatever). Then, the person doing 2nd entry is supposed to fix the
errors. Since the errors can happen in either first entry or second,
it seems easiest and most intuitive to have a 3rd "Validation " tab on
the main entry form with a subform filtered to shows only the
discrepancies. (Tabs 1 and 2 are for first and 2nd entry,
respectively). Entry 1 would be right next Entry 2, the person doing
data entry checks the hard copy and fixes whichever entry has the
mistake.

With 1 record storing both entries, the subform for the "validation
tab" is very easy to design. It's very easy to query for the
discrepancies, and easy to have an updateable dataset. With two
tables and/or two records, the "Validation tab" seems to require 2
subforms (one for each entry -- so the two entries line up
horizontally next to each other). Additionally, filtering/ querying
for the discrepancies is harder (although that may just be me).

Please let me know your concerns with this table structure! I think
the intuitively easy-to-use and slightly faster end product, justifies
this structure. I was a bit wary of increasing the number of variables
in the table, but there seems to be no real reason against it or
disadvantage to
this!

Thanks,
Paul

[David W. Fenton]

Re: Microsoft Access, Double Data Entry and breaking the Normalization rule

"hippomedon@goo glemail.com" <hippomedon@goo glemail.comwrot e in
news:1180560289 .563640.50610@k 79g2000hse.goog legroups.com:

On May 30, 1:44 pm, "David W. Fenton"
<XXXuse...@dfen ton.com.invalid wrote:

>"hippome...@go oglemail.com" <hippome...@goo glemail.comwrot e
>innews:1180540 025.677581.2526 70@o5g2000hsb.g ooglegroups.com :
>>

Currently the db is set-up with option 2 (two records in same
table).
This works very well for validation purposes, when the
validation is done after the entry. The VBA code that I
developed easily compares Entry 1 to Entry 2 and generates an
output. However, for instantaneous validation, it does not work
as well. This is the main reason I'm considering switching to
option 1. I think it would allow for an easier and more
instantaneous comparison of entry 2 to entry 1. It should
actually only add 2 fields to each record (one for the
response, and one to record the time of entry).

>>
>I don't see why you can't load the other record in a recordset
>and compare the values field by field when they are entered into
>the other table. That would be just as easy as comparing to a
>different field. You'd do it something like this in the
>BeforeUpdate of the control:
>>
> If Nz(Me!ControlNa me) <Nz(rs(Me!Contr olName.ControlS ource))
> Then
> ...
>>
>You could also write a function that uses Screen.ActiveCo ntrol to
>do it, which makes it easy to assign to all the controls (in the
>above, replace Me!ControlName with Screen.ActiveCo ntrol).
>>
>--
>David W. Fenton http://www.dfenton.com/
>usenet at dfenton dot com http://www.dfenton.com/DFA/

>
This certainly works, although I find that it's slightly slower
than comparing to another field.

Why? The only difference would be the time it takes to open the
recordset on the existing record, and that's going to be very small
(unless you do it for each field, which makes no sense -- instead
you'd do it once for the whole record, as soon as it's been
determined what the matching record is).

I'm curious about your concerns for having
two fields, instead of two tables.

Or two records in the same table.

It's more normalized and I see no advantage whatsoever in using two
fields in the same record. I like the elegance of using the same
field name to compare, as opposed to lopping off the last character
and using a new last character.

My big problem with this method is making it aesthetically
pleasing
and/or easy to use for the end user. I'll try to explain the
process a little better to show what I mean. Typically, it's
fastest to enter the 500 or so responses at each evaluation in one
shot. The validation is happening either silently in the
background or done immediately after the data has been 2nd entered
(by pressing a command button, or whatever). Then, the person
doing 2nd entry is supposed to fix the errors. Since the errors
can happen in either first entry or second, it seems easiest and
most intuitive to have a 3rd "Validation " tab on the main entry
form with a subform filtered to shows only the discrepancies.
(Tabs 1 and 2 are for first and 2nd entry, respectively). Entry 1
would be right next Entry 2, the person doing data entry checks
the hard copy and fixes whichever entry has the mistake.
>
With 1 record storing both entries, the subform for the
"validation
tab" is very easy to design. It's very easy to query for the
discrepancies, and easy to have an updateable dataset. With two
tables and/or two records, the "Validation tab" seems to require 2
subforms (one for each entry -- so the two entries line up
horizontally next to each other). Additionally, filtering/
querying for the discrepancies is harder (although that may just
be me).

I don't see how it's harder. You can get them into a single row with
nothing more than joining the two tables togetner and then referring
to the field names fully specified with table name.

Please let me know your concerns with this table structure! I
think the intuitively easy-to-use and slightly faster end product,
justifies this structure. I was a bit wary of increasing the
number of variables in the table, but there seems to be no real
reason or disadvantage to this!

Well, that raises the question of whether it's correct to structure
a survey with many fields in one record, or if each survey question
should be its own record. I definitely lean towards the latter, as
it makes a whole host of things much, much easier.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/