Comparing Two Identical Tables - Loop Through fields in a Recordset

[gillianbrooks91@googlemail.com]

Forgive me for asking this question, I've trawled through nearly every
available post on this subject that I can find for a few weeks now but
nothing quite points me in the right direction.

I'm quite new to trying to mess around with VB and ADO within MS
Access and have realised the steep learning curve I have, but, I want
to try and solve this problem quickly and was wondering if anyone
would help me out??

I want to be able to compare two tables within the same .mbd and write
out any mismatched fields to a third table. From what I can gather
then this is best done via ADO and looping through recordsets of the
two tables and writing to a recordset of the third table? The two
tables are revisions of the same query written to different tables, so
the structure is exactly the same.

Table 1 and 2 have :

Field1, Field2, Field3, Field4, Field5, Field6, Field7

Where Field 1 is the Primary Key for the both tables.

I'd like to loop through each record comparing each field in Table1 to
it's corresponding field in Table2 where Table1.Field1 = Table2.Field1
and write out where there is a difference to a third table, where the
two fields are matching (no change), I'd like to put a null value; I
could then report on Table 3

So for example, if one record in each table looked like so:

Table1
Field1 = A
Field2 = B
Field3 = C
Field4 = D
Field5 = E
Field6 = F
Field7 = G

Table2
Field1 = A
Field2 = B
Field3 = CC
Field4 = D
Field5 = EE
Field6 = F
Field7 = GG

Then the resulting Table 3 would look like this

Field1 = A
Field2 = '' (Null)
Field3 = CC
Field4 = '' (Null)
Field5 = EE
Field6 = '' (Null)
Field7 = GG

Can anyone help out a damsel in distress? I'm using MS Access 2003

Thanks

Gill xx

[DFS]

Re: Comparing Two Identical Tables - Loop Through fields in a Recordset

gillianbrooks91 @googlemail.com wrote:

Forgive me for asking this question, I've trawled through nearly every
available post on this subject that I can find for a few weeks now but
nothing quite points me in the right direction.
>
I'm quite new to trying to mess around with VB and ADO within MS
Access and have realised the steep learning curve I have, but, I want
to try and solve this problem quickly and was wondering if anyone
would help me out??
>
I want to be able to compare two tables within the same .mbd and write
out any mismatched fields to a third table. From what I can gather
then this is best done via ADO and looping through recordsets of the
two tables and writing to a recordset of the third table? The two
tables are revisions of the same query written to different tables, so
the structure is exactly the same.
>
Table 1 and 2 have :
>
Field1, Field2, Field3, Field4, Field5, Field6, Field7
>
Where Field 1 is the Primary Key for the both tables.
>
I'd like to loop through each record comparing each field in Table1 to
it's corresponding field in Table2 where Table1.Field1 = Table2.Field1
and write out where there is a difference to a third table, where the
two fields are matching (no change), I'd like to put a null value; I
could then report on Table 3
>
So for example, if one record in each table looked like so:
>
Table1
Field1 = A
Field2 = B
Field3 = C
Field4 = D
Field5 = E
Field6 = F
Field7 = G
>
Table2
Field1 = A
Field2 = B
Field3 = CC
Field4 = D
Field5 = EE
Field6 = F
Field7 = GG
>
Then the resulting Table 3 would look like this
>
Field1 = A
Field2 = '' (Null)
Field3 = CC
Field4 = '' (Null)
Field5 = EE
Field6 = '' (Null)
Field7 = GG
>
Can anyone help out a damsel in distress? I'm using MS Access 2003
>
Thanks
>
Gill xx

Build a query that compares each column in the two tables, and joins on the
common ID column. This code won't be perfect but will help get you started.

dim db as database, rs as recordset, cSQL as string
set db = currentdb()

cSQL = "SELECT T1.ID, "

Set rs = db.OpenRecordse t("JanData")
For i = 1 To rs.Fields.Count - 1


'COMPARE NUMERIC DATA - ABSOLUTE % CHANGE
cSQL = cSQL & "Format(Abs (T1.[" & rs(i).Name & "] - T2.[" & rs(i).Name &
"])/T2.[" & rs(i).Name & "],'0.00%') AS " & rs(i).Name & ", "

'COMPARE TEXT DATA
cSQL = cSQL & "IIf(T1.[" & rs(i).Name & "] <T2.[" & rs(i).Name &
"],'Diff','Match' ) AS " & rs(i).Name & ", "

Next i
rs.Close
Set rs = Nothing
cSQL = Trim(cSQL)
cSQL = Left(cSQL, Len(cSQL) - 1)

cSQL = cSQL & " FROM JanData T1 INNER JOIN FebData T2 ON T1.ID = T2.ID "

debug.print cSQL

Then cut and paste from the Immediate Window into a query window and run it.

[Salad]

Re: Comparing Two Identical Tables - Loop Through fields in a Recordset

gillianbrooks91 @googlemail.com wrote:

Forgive me for asking this question, I've trawled through nearly every
available post on this subject that I can find for a few weeks now but
nothing quite points me in the right direction.
>
I'm quite new to trying to mess around with VB and ADO within MS
Access and have realised the steep learning curve I have, but, I want
to try and solve this problem quickly and was wondering if anyone
would help me out??
>
I want to be able to compare two tables within the same .mbd and write
out any mismatched fields to a third table. From what I can gather
then this is best done via ADO and looping through recordsets of the
two tables and writing to a recordset of the third table? The two
tables are revisions of the same query written to different tables, so
the structure is exactly the same.
>
Table 1 and 2 have :
>
Field1, Field2, Field3, Field4, Field5, Field6, Field7
>
Where Field 1 is the Primary Key for the both tables.
>
I'd like to loop through each record comparing each field in Table1 to
it's corresponding field in Table2 where Table1.Field1 = Table2.Field1
and write out where there is a difference to a third table, where the
two fields are matching (no change), I'd like to put a null value; I
could then report on Table 3
>
So for example, if one record in each table looked like so:
>
Table1
Field1 = A
Field2 = B
Field3 = C
Field4 = D
Field5 = E
Field6 = F
Field7 = G
>
Table2
Field1 = A
Field2 = B
Field3 = CC
Field4 = D
Field5 = EE
Field6 = F
Field7 = GG
>
Then the resulting Table 3 would look like this
>
Field1 = A
Field2 = '' (Null)
Field3 = CC
Field4 = '' (Null)
Field5 = EE
Field6 = '' (Null)
Field7 = GG
>
Can anyone help out a damsel in distress? I'm using MS Access 2003
>
Thanks
>
Gill xx

I might consider creating an append or maketable query. Add your two
tables to it and make a linkline between the two Field1's. Then create
a series of IIF() statements. Ex:
F2 : IIF(NZ(Table1!F ield2,"") = NZ(Table2!Field 2,""),Table1!Fi eld2,Null)

Do the same for the rest of the fields.

Now run it. If acceptable, make the query type Append to append to
table3 or MakeTable to create table3.

Party

http://www.youtube.com/watch?v=aUUGblNjK20

[gillianbrooks91@googlemail.com]

Re: Comparing Two Identical Tables - Loop Through fields in aRecordset

On 21 Feb, 11:13, gillianbrook... @googlemail.com wrote:

Lyle, thank you for this example ... I tried to replicate in the
Northwinds database, by putting the new public function in a standard
module, but when I run the query Access tells me that there is an
'Undefined Function 'NullIfEqual' in the expression' ... what could I
be doing wrong? As far as I can tell everything is correct and nothing
has been misspelt??

I should add that I've noted the bug with MSA 2003 that's being
referenced here (http://support.microsoft.com/kb/824277) and I don't
think it's applicable, in this scenario I don't find any MISSING
references in the Visual Basic Editor ...

[lyle fairfield]

Re: Comparing Two Identical Tables - Loop Through fields in a Recordset

gillianbrooks91 @googlemail.com wrote in news:c21ff808-4819-4572-b4bf-
f4b8d36e4d2d@h1 1g2000prf.googl egroups.com:

On 21 Feb, 11:13, gillianbrook... @googlemail.com wrote:
>

>Lyle, thank you for this example ... I tried to replicate in the
>Northwinds database, by putting the new public function in a standard
>module, but when I run the query Access tells me that there is an
>'Undefined Function 'NullIfEqual' in the expression' ... what could I
>be doing wrong? As far as I can tell everything is correct and nothing
>has been misspelt??

>
I should add that I've noted the bug with MSA 2003 that's being
referenced here (http://support.microsoft.com/kb/824277) and I don't
think it's applicable, in this scenario I don't find any MISSING
references in the Visual Basic Editor ...
>

You might want to check this a bit more with

Sub checkRefs()
Dim r As Reference
For Each r In References
Debug.Print r.Name, r.BuiltIn, r.IsBroken
Next r
End Sub


--
lyle fairfield

I will arise and go now,
For always night and day
I hear lake water lapping
With low sounds by the shore;
While I stand on the roadway
Or on the pavements gray,
I hear it in the deep heart's core.
- Yeats