Trying to compar all the fields between two tables for all records

[jakeir]

Hi,
I am going to be given two list of records which I am going to put in two different tables.
so one list I am going to put into table A and list 2 I am putting in table B. List 2 is the same data as list 1 except list 1 has Septembers data and list 2 has Octobers data; and both list has about 12 columns each.
What they want me to do is actually compare, for each record, each column of data and see if there is a change.
So if in record1 columnA = 21 and ColumnB = 43
and in record2 columnA = 21 and ColumnB = 22
this would be shown as a record that changed

The problem is that I do not know how you would write a select statement to compare column by column like this, can some one please help me.
Thank you

 

[dhookom]

Check out thread705-1576253. Keep in mind there are no record numbers in Access. Hopefully you have a primary key in both tables so they can be joined.

Duane
Hook'D on Access
MS Access MVP

 

[jakeir]

No the problem is that htere is no primary key. and even if there was they want me to look and see is there is a difference in any of the columns.
So I do not known, but there should be a way to caompare field by field.
Thank you

 

[dhookom]

Whoever "they" are, tell them a record in one table can't be compared to a record in another table unless the records are related.

You refer in your original post to Record1 and Record2. There is no 1st, 2nd, or 3rd record in table. Picture two bags of rocks. How do you identify which rock in bag A gets compared to which rock in bag B?

Duane
Hook'D on Access
MS Access MVP

 

[Golom]

I would create two tables with an autonumber field followed by the columns that you need. Load each of the tables with your data and make sure that you insert it in the order that you want. You will need to use VBA for that since SQL makes no allowance for ordering and offers no guarantees for which record is inserted first, second, third, etc.

Note that you will need to

Delete * From Table1
Alter Table Table1 Alter Column ANum Counter (1,1)
Delete * From Table2
Alter Table Table2 Alter Column ANum Counter (1,1)

for each of the tables before you begin. That just assures that your autonumbers start at 1 for each run.

Then you do some coding

Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim fld As DAO.Field
Dim db  As DAO.Database
Set db = CurrentDB()

Set rs1 = db.OpenRecordset(Select * From Table1 Order By Anum")
Set rs2 = db.OpenRecordset(Select * From Table2 Order By Anum")

Do Until rs1.EOF
    For Each Fld in rs1.Fields
       If Fld.Name <> "ANum" Then
          If rs2.Fields(Fld.Name).Value <> rs1.Fields(Fld.Name).Value Then
             Debug.Print "Field " & Fld.Name & _
                         " does not match on record " & rs1![ANum]
          End If
        End If
     Next
     rs1.MoveNext
     rs2.MoveNext
Loop

I have omitted the code to check for unequal numbers of records in the two recordsets.

All this is very Ad Hoc and somewhat of an abuse of how relational databases are supposed to work but, if you have to deal with what "THEY" want then this is a workaround.

I have used DAO in this example but there are equivalent structures in ADO if that's what you're using.