Double data entry

[mlthomas007 via AccessMonster.com]

Hi,

We are in the process of entering data into a new data base (20 tables).

The guidelines for the grant we are under states we must perform dual data
entry in case there is an audit. I don't like this rule however it needs to
be worked out.

We want to take the best approach to this.

Is there a best way to have a copy of each table and direct data entry from
the forms into Table1 and Table2?

Thanks so much,
Mel

 

[Klatuu]

Are you saying you are required to maintain duplicate copies of your data, or
that you need to have duplicate data entry done, then compared against each
other for accuracy? How this requirement is defined will help determine the
correct method.

I have never heard of a requirement to maintain duplicate copies, just
duplicate data entry.

 

[mlthomas007 via AccessMonster.com]

Hi,

Duplicate entry so we can check for accuracy.

Thanks Klatuu.

Melissa

Are you saying you are required to maintain duplicate copies of your data, or
that you need to have duplicate data entry done, then compared against each
other for accuracy? How this requirement is defined will help determine the
correct method.

I have never heard of a requirement to maintain duplicate copies, just
duplicate data entry.

Hi,

[quoted text clipped - 11 lines]

Thanks so much,
Mel

 

[Klatuu]

Then two tables that have the exact same schema as your production table will
do it. As to how to implement this, I prefer to use only one form with
multiple options. That way, you only have one form to maintain.

In your case, you will want 3 options
1. Production
You will not want to allow addtions here. It would negate your audit
controls. I don't know what business rules you have on edits. If they are
allowed, no action is required. If no edits are allowed, set Allow Edits to
False. If only some fields are allowed to be edited, then you will have to
control with Allow Edits = True and Enable only those controls that are
allowed to be edited. For a control where editing is not allowed, I usually
set Enabled = False and Locked = True. It is not greyed out and it wont take
the focus. Whether you allow deletes depends on your business rules and/or
regulatory requirements.
2. Data Entry 1. Make it a Data Entry form. That way, it will only allow
new records to be added.
3. Data Entry 2. Same as Data Entry 2.

Now to set the form up to use, you will want to use the OpenArgs argument of
the OpenForm method when you open the form so the form will know how to
configure itself.

Then, in the Load event of the form, use the OpenArgs property of the form
to determine how to configure the form. For example:

With Me
Select Case .OpenArgs
Case Is 1 'Production
.AllowAdditions = False
.RecordSouce = "tbllProductionData"
etc.
Case Is 2 'Data Entery 1
.DataEntry = True
.RecordSource = "tblEntryOne"
etc.
Case Is 3 'Data Entry 2
.DataEntry = True
.RecordSource = "tblEntryTwo"
etc.
End Select
End With

Then you will, of course, need a process to compare the two tables and
update the corrected data to the production table and empty the data entry
tables before doing another session of data entry.
--
Dave Hargis, Microsoft Access MVP

Hi,

Duplicate entry so we can check for accuracy.

Thanks Klatuu.

Melissa

Are you saying you are required to maintain duplicate copies of your data, or
that you need to have duplicate data entry done, then compared against each
other for accuracy? How this requirement is defined will help determine the
correct method.

I have never heard of a requirement to maintain duplicate copies, just
duplicate data entry.

Hi,

[quoted text clipped - 11 lines]

Thanks so much,
Mel

 

[John Spencer]

One way to handle this is to add bound controls for all the data entry items
to your form and then add a second set of unbound controls that parallels the
bound controls.

Then you will need a VBA routine that checks the values in each of the bound
controls against the corresponding unbound controls and gives you an error
message.

To cut down on the coding, I would assign names to the controls like
txtLastName and txtLastName_Check
and assign a tag to the bound control such as "Dual". Then you could loop
through the controls collection, look for the tag and then compare the values
in the bound and corresponding unbound controls. If mismatches appear don't
allow updating/saving the data.

UNTESTED AIRCODE (off the top of my head)
Dim ctlAny as Control
Dim ctlDual as Control
For each ctlAny in Me.Controls
if CtlAny.Tag = "Dual" then
CtlDual = ME(CtlAny.Name & "_Check"
IF CtlAny = CtlDual OR
IsNull(CtlAny) and IsNull(CtlDual) Then
'Do Nothing
Else
'Do stuff here to flag the fields or
'report the mismatched fields and
'set a flag to cancel the update/insert
End IF

End if

Next ctlAny

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi,

Duplicate entry so we can check for accuracy.

Thanks Klatuu.

Melissa

Are you saying you are required to maintain duplicate copies of your data, or
that you need to have duplicate data entry done, then compared against each
other for accuracy? How this requirement is defined will help determine the
correct method.

I have never heard of a requirement to maintain duplicate copies, just
duplicate data entry.

Hi,

[quoted text clipped - 11 lines]

Thanks so much,
Mel

 

[Larry Daugherty]

Mel,

I would verify exactly what the words in the requirement for "dual
data entry" mean. I suspect that the intent is that there be two
entirely separate data entry streams of each data element.

Going on the above assumption:

Neither person entering the data should know or care about anyone else
entering the same data. There should especially be no "steering" of
the data that would occur if the person entering the "later" data
stream were constantly influenced by what had been entered in the
parallel, "first", data stream. In other words, I'd assume that the
intent was that totally independent operators view and interpret the
input data and enter the data as they, independently, interpret it to
be. There should be no bar to having more than one data stream being
entered simultaneously by separate typists. The validating or
cross-checking process should not begin until all data streams of the
same data have been completed. It should be part of the checking and
resolution process to display the elements that disagree and to
resolve them by some designed process. The resolution process should
be specified as part of the over-all application.

The long and the short of it is that the business requirements must be
clearly defined before the technical solutions are designed and
implemented.

HTH

 

[mlthomas007 via AccessMonster.com]

Hi,

The requirements for dual data entry are imposed so two persons will enter
the same data to be error checked. They will use the same hard copy source
to do so. The study grant from the IRB has been doing this for years but the
issue we are running into is how to set this up best in access. Using an
access database that has already been developed without being normalized.

Thanks,
Mel

Mel,

I would verify exactly what the words in the requirement for "dual
data entry" mean. I suspect that the intent is that there be two
entirely separate data entry streams of each data element.

Going on the above assumption:

Neither person entering the data should know or care about anyone else
entering the same data. There should especially be no "steering" of
the data that would occur if the person entering the "later" data
stream were constantly influenced by what had been entered in the
parallel, "first", data stream. In other words, I'd assume that the
intent was that totally independent operators view and interpret the
input data and enter the data as they, independently, interpret it to
be. There should be no bar to having more than one data stream being
entered simultaneously by separate typists. The validating or
cross-checking process should not begin until all data streams of the
same data have been completed. It should be part of the checking and
resolution process to display the elements that disagree and to
resolve them by some designed process. The resolution process should
be specified as part of the over-all application.

The long and the short of it is that the business requirements must be
clearly defined before the technical solutions are designed and
implemented.

HTH

Hi,

[quoted text clipped - 11 lines]

Thanks so much,
Mel

 

[Larry Daugherty]

Hi Melissa,

I came late to the party and reacted to the content of John's response
without having read the earlier elements of the thread. Since you
responded to my post I read the whole thread and see that you had
already unambiguously defined what you needed regarding data entry.

An aspect that seems to have been lost in the responses is that you
are vulnerable to audits for some span of time after the data entry.
To that end, I would not delete any data entered by anyone until the
times for audits have long since passed. While accuracy of the data
is the intent, being able to prove that you did it all according to
the terms of the grant are your ultimate fallback. The best way to
prove that you had more than one data entry stream is to retain all of
the data streams.

To get more specific and applicable guidance you'll have to
communicate the business rules for the whole process. If those rules
haven't been given to you then you'll have to make them up by
analyzing the requirements and designing the solutions. How will you
identify a particular user and data stream? Will each user & stream
combination eventually be locked from further editing? How long can a
user return to edit the stream? Can a supervisor edit someone else's
stream? (not a good idea. better to have the supervisor create
her/his own audit stream).

Apparently some significant amount of thought has already gone into
the process as you say that there are 20 tables. Knowing the
structure of your data might also help.

FWIW I would keep all of the data streams and the production data in
the same set of tables. There should be an attribute within the
"main" record that identifies its provenance. With proper
relationships, only that "main" record needs that attribute. There
can be a separate table that holds the values of that attribute such
as "Audit", "Entry" and "Production". Per relational rules, all
entities of the same type belong within the same table. To separate
entities into different tables based on some attribute is not a good
idea. It's tantamount to a car dealerships having separate tables in
their databases for each color of car: tblRecCars, tblBlueCars,
tblWhiteCars, etc. Color is obviously an attribute and its value
should appear in a field in a record.

It seems that you'd like is for someone to come up with a good process
for the most complex part of the whole process: Comparing the data
streams and resolving data conflicts by selection (and probably
further editing) while simultaneously referring to the input data
documents. That can be done but it requires a very good understanding
of your entire process, its data structures and workflows.

HTH
--
-Larry-
--

Hi,

The requirements for dual data entry are imposed so two persons will enter
the same data to be error checked. They will use the same hard copy source
to do so. The study grant from the IRB has been doing this for years but the
issue we are running into is how to set this up best in access. Using an
access database that has already been developed without being normalized.

Thanks,
Mel

Mel,

I would verify exactly what the words in the requirement for "dual
data entry" mean. I suspect that the intent is that there be two
entirely separate data entry streams of each data element.

Going on the above assumption:

Neither person entering the data should know or care about anyone else
entering the same data. There should especially be no "steering" of
the data that would occur if the person entering the "later" data
stream were constantly influenced by what had been entered in the
parallel, "first", data stream. In other words, I'd assume that the
intent was that totally independent operators view and interpret the
input data and enter the data as they, independently, interpret it to
be. There should be no bar to having more than one data stream being
entered simultaneously by separate typists. The validating or
cross-checking process should not begin until all data streams of the
same data have been completed. It should be part of the checking and
resolution process to display the elements that disagree and to
resolve them by some designed process. The resolution process should
be specified as part of the over-all application.

The long and the short of it is that the business requirements must be
clearly defined before the technical solutions are designed and
implemented.

HTH

Hi,

[quoted text clipped - 11 lines]

Thanks so much,
Mel