|
|
|
Comparing two Access tables |
|---|
| message from =?Utf-8?B?THlu?= on 10 May 2004 |
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 replied to =?Utf-8?B?THlu?= on 10 May 2004 |
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 replied to Jeff Boyce on 10 May 2004 |
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.
|
| =?Utf-8?B?THlu?= replied to Brendan Reynolds on 10 May 2004 |
Thank you for your replies Jeff and Brendan
"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.
I think it is a matter of dream on.......!
Thanks anyway
Lyn
|
| Brendan Reynolds replied to =?Utf-8?B?THlu?= on 10 May 2004 |
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.
|
| Pieter Wijnen replied to Brendan Reynolds on 11 May 2004 |
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
"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
news:uO96BcqNEHA.3936@TK2MSFTNGP12.phx.gbl...
|
| =?Utf-8?B?THlu?= replied to Pieter Wijnen on 11 May 2004 |
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
|
| =?Utf-8?B?THlu?= replied to Brendan Reynolds on 11 May 2004 |
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.
Thanks
Lyn
|
| Jeff Boyce replied to Brendan Reynolds on 10 May 2004 |
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" <brenreyn at indigo dot ie> wrote in message
news:%23dl63DpNEHA.3816@TK2MSFTNGP12.phx.gbl...
|
| Brendan Reynolds replied to Jeff Boyce on 10 May 2004 |
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.
|
|
