Comparing two Access tables

[Guest]

My project requires double data entry - stipulated in the protocol
How can I compare two tables to check for any differences
Is there a neat way of doing this without having to produce a horrendous query
Any suggstions

 

[Jeff Boyce]

Lyn

"Double-injury" bookkeeping may be necessary when human clerks are entering
numbers on paper ledgers. How does writing the same value into two tables
ensure against data entry error -- or are you saying you are requiring the
same data element to be entered two different times? This would not
necessarily require two tables.

 

[Brendan Reynolds]

It's a method of data validation, Jeff. The same data is entered twice, by
two different data entry operators, and the two copies are compared. If both
people entered exactly the same data, it's considered valid, on the theory
that it's unlikely that both operators would make exactly the same mistake.

The only way I can think of to avoid having to compare the data on a
field-by-field basis would be to compute some kind of checksum and compare
that instead.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Jeff Boyce]

Brendan

Yah ... I understand the rationale. I was just giving Lyn a hard time about
the perceived need to use two tables <g>.

There are so many other issues I can imagine/invent ... for example, if the
second version entered doesn't match the first, who's right?! And in
correcting the value, who checks to ensure that the correction is correct?!!

One approach might be to have a single table, data entered via a form the
first time, then the second person does data entry in the same form "over
the top" of the first. If the form "sees" a difference when a field value
is entered, it flags that for re-check.

JOPO (just one person's opinion)

Jeff Boyce
<Access MVP>

 

[Brendan Reynolds]

I believe it's usually used (when it is used at all) with data that is being
entered from hard-copy, like the forms that Lyn mentions. If the two
versions don't match, you refer back to the hard copy.

I've never actually seen it done in the "real world", it's just something I
remember from a course I did once, and for a project I worked on some years
back, the requirements were "either double entry or scanning" - we went for
scanning.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Brendan Reynolds]

I don't think double entry is very often used these days, Lyn. I can only
remember seeing one other question about it in the newsgroups. So I doubt
we'll see explicit support for it in Access - I don't think there'd be
enough demand to justify it.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Thank you for your replies Jeff and Brendan

My own view on double data entry into a sophisticated system like Access,

where you can build in logic, range and condition checks at data entry, is
that it is a lot of extra work for possibly little gain in accuracy.

The bit in the Protocol which I have to abide by, states:
"All study data recorded on the CRF will be subject to independent double

data entry using a validated database programme. Comparison of the data
entries will then be performed and any resulting discrepancies adjudicated
by an independent third person with reference to the Case Record Form...."

This might ensure that what was on the form was correctly entered but it

does not guarantee that what is on the form is correct in the first place.

It would be great if Access had a facility for taking two such tables and

comparing each record by whizzing through the fields, from Field1 to Fieldx,
and checking the data in each one is the same and listing discrepancies,
without having to worry about field names etc.

 

[Guest]

It's all to do with GCP Guidelines (Good Clinical Practice) and European directives etc.. It is for a Pharmaceutical Company who are always hot on double data entry. I think they need to be strict on this if they want to get new drugs licensed etc. although this study is not actually a drug trial.
If we were doing the data entry ourselves I would just set up a system to write out the data to text files and use another program to compare them, but I was hoping to do it all within the Access setup so I guess it will have to be the longwinded way
Thank
Ly

----- Brendan Reynolds wrote: ----

I don't think double entry is very often used these days, Lyn. I can onl
remember seeing one other question about it in the newsgroups. So I doub
we'll see explicit support for it in Access - I don't think there'd b
enough demand to justify it

--
Brendan Reynolds (MVP
http://brenreyn.blogspot.co

The spammers and script-kiddies have succeeded in making it impossible fo
me to use a real e-mail address in public newsgroups. E-mail replies t
this post will be deleted without being read. Any e-mail claiming to b
from brenreyn at indigo dot ie that is not digitally signed by me with
GlobalSign digital certificate is a forgery and should be deleted withou
being read. Follow-up questions should in general be posted to th
newsgroup, but if you have a good reason to send me e-mail, you'll fin
a useable e-mail address at the URL above

Thank you for your replies Jeff and Brenda

where you can build in logic, range and condition checks at data entry, i
that it is a lot of extra work for possibly little gain in accuracy

The bit in the Protocol which I have to abide by, states
"All study data recorded on the CRF will be subject to independent doubl

data entry using a validated database programme. Comparison of the dat
entries will then be performed and any resulting discrepancies adjudicate
by an independent third person with reference to the Case Record Form....

This might ensure that what was on the form was correctly entered but i

does not guarantee that what is on the form is correct in the first place

It would be great if Access had a facility for taking two such tables an

comparing each record by whizzing through the fields, from Field1 to Fieldx
and checking the data in each one is the same and listing discrepancies
without having to worry about field names etc

I think it is a matter of dream on.......

Thanks anywa Ly
----- Brendan Reynolds wrote: ----
It's a method of data validation, Jeff. The same data is entere

twice, b
two different data entry operators, and the two copies are compared If bot
people entered exactly the same data, it's considered valid, on th theor
that it's unlikely that both operators would make exactly the sam mistake

The only way I can think of to avoid having to compare the data on

field-by-field basis would be to compute some kind of checksum an compar
that instead

--

Brendan Reynolds (MVP
http://brenreyn.blogspot.co

The spammers and script-kiddies have succeeded in making i

impossible fo
me to use a real e-mail address in public newsgroups. E-mail replie t
this post will be deleted without being read. Any e-mail claiming t b
from brenreyn at indigo dot ie that is not digitally signed by m with
GlobalSign digital certificate is a forgery and should be delete withou
being read. Follow-up questions should in general be posted to th
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Pieter Wijnen]

Interesting thread ;-)
It is not very hard to do however..
In this Example I presume a "Key" Field as being unique for the data & that
The Tables have the exact same structure
also "errounous" data is logged in a "log" table

Sub CheckTables(Byval Tab1 As String Tab2 As String,ByVal KeyField as
string)

Dim Db AS DAO.Database
Dim Rs1 AS DAO.Recordset, Rs2 AS DAO.Recordset
Dim Fld AS DAO.Field
Dim SQL as string

Set Db = CurrentDb()
sql = "INSERT INTO LOG(KEY,FIELD,VALUE1) SELECT KEY,'" & Table1 &
"','<UnMatched>') FROM [" & Table1 & "] A " & _
"WHERE NOT EXISTS (SELECT 'X' FROM [" & Table2 & "]" B WHERE B.[" &
KeyField & "] = A.[" & KeyField & "])"
Db.Execute SQL
sql = "INSERT INTO LOG(KEY,FIELD,VALUE2) SELECT KEY,'" & Table2 &
"','<UnMatched>') FROM [" & Table2 & "] A " & _
"WHERE NOT EXISTS (SELECT 'X' FROM [" & Table1 & "]" B WHERE B.[" &
KeyField & "] = A.[" & KeyField & "])"
Db.Execute SQL ' Any key values unmatched

SQL="SELECT * FROM [" Table1 & "] A WHERE EXISTS (SELECT 'X' FROM [" &
Table2 & "] B WHERE B.[" & KeyField & "] = A.[" & KeyField & "])"
Set Rs1 = Db.OpenRecordset(SQL,DbOpenSnapshot)
While Not Rs1.EOF
' Assume easy value transition for keyfield - could have made it more
robust but ...
SQL = "SELECT * FROM [" & Table2 & "] B WHERE B.[" & KeyField & "] = '" &
Rs1.Fields(KeyField) & "'"
Set Rs2 = Db.OpenRecordset(SQL, DbOpenSnapshot) ' Match is ensured through
Rs1
For each fld In Rs1.Fields
If (IsNull(Fld.Value ) And Not IsNull(Rs2.Fields(Fld.Name).Value) Or
(Not IsNull(Fld.Value ) And IsNull(Rs2.Fields(Fld.Name).Value) Then ' Empty
field on 1 side
SQL = "INSERT INTO LOG (KEY,FIELD,VALUE1,VALUE2) " & _
"VALUES ("' & Rs1.Fields(KeyField).Value & "','" &
Fld.Name & "','" & Nz(Fld.Value,"NULL") & "',','" & Nz(Fld.Value,"NULL")
&")"
Db.Execute SQL
ElseIf IsNull(Fld.Value) Then
' Do Nothing
ElseIf Fld.Value <> Rs2.Fields(Fld.Name).Value Then
SQL = "INSERT INTO LOG (KEY,FIELD,VALUE1,VALUE2) " & _
"VALUES ("' & Rs1.Fields(KeyField).Value & "','" &
Fld.Name & "','" & Nz(Fld.Value,"NULL") & "',','" & Nz(Fld.Value,"NULL")
&")"
' Don't need Nz but if Xerox can do it ...
Db.Execute SQL
Else
' Do Nothing
End If
Next
Rs2.Close : Set Rs2 = Nothing
Rs1.MoveNext
Wend
Rs1.Close : Set Rs1 = Nothing
Set Db = Nothing
End Sub

... bar any typos - this ought to do it


HTH

Pieter

 

[Guest]

Thanks for that Pieter.
Piece of cake eh
I am not sure if my programming and SQL expertise is quite up to scratch but I basically follow what you are doing
I take it Fld.Value and Fld.Name are properties of a Field type variable. What other properties can Fld have? Is there a Fld.Number property for the field number or would this be a property of the Recordset? I see you use an expression
Rs2.Fields(Fld.Name).Value. Is there any way I can use a field number in this so that I could loop through the fields from 1 to however many there are

Thanks for your help anyway
Lyn