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

On May 30, 1:44 pm, "David W. Fenton" <XXXuse...@xxxxxxxxxxxxxxxxxxx>
wrote:
"hippome...@xxxxxxxxxxxxxx" <hippome...@xxxxxxxxxxxxxx> wrote innews:1180540025.677581.252670@xxxxxxxxxxxxxxxxxxxxxxxxxxx:

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!ControlName) <> Nz(rs(Me!ControlName.ControlSource)) Then
...

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

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

.

Quantcast