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

gillianbrooks91@xxxxxxxxxxxxxx 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.OpenRecordset("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.



.

Relevant Pages

  • Re: recordset.MovePrevious
    ... You need to cut & paste the exact code you are using - from & including the ... > to compare 2 items in a query based recordset. ... > Value is the name of the column in the recordset. ... > to look at the value, store it as a variable, then look at ...
    (microsoft.public.access.modulesdaovba)
  • Re: recordset handling question
    ... I left out the compare code because I did not ... I did include the part I need help with (writing the recordset to ... as to row/recordset, i was not sure what other would know it as, so I ... Please clarify how you are comparing. ...
    (comp.databases.ms-access)
  • Re: Update an SQL database
    ... Try using Echo for every row in the recordset, ... If objRecordSet.Fields= strServerName Then ... want to compare my variable with the record in the database, ...
    (microsoft.public.data.ado)
  • Re: Comparing two DataSets with a Merge
    ... An ADO.NET RecordSet keeps track of the previous and current ... You can then call the GetChanges() ... >> been changed over time offsite and saved in an access database. ... >> take advantage of versions in the dataset to compare previous and current ...
    (microsoft.public.dotnet.framework.adonet)