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

"hippomedon@xxxxxxxxxxxxxx" <hippomedon@xxxxxxxxxxxxxx> wrote in
news:1180560289.563640.50610@xxxxxxxxxxxxxxxxxxxxxxxxxxxx:

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.

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

Quantcast