Compare 2 tbls, all fields, rpt non-matching

[AlanM]

Hi All

My problem:
We update our accounting system customer details from an A2K db, via an export/import/ODBC interface. Works fine.

Unfortunately the accounting system will not overwrite existing data with blank/null information. So when a customer's address changes from a two line address to a one line address, the accounting system retains the old data in the Address Line 2 field! Same for customers who no longer have a fax machine for example; we cannot "delete" the fax number in the accounting system via the import.

So to minimise the amount of work involved in updating the accounting system manually, (it will be a customer by customer process, and many screens per customer to be viewed to correct the old data), I want to produce a report showing the current Access information followed by the current Accounting system information (which I have via import to Access), BUT only for those customers where "field pairs" do not match e.g. AccountingFax <> AccessFax.

The two tables will have the same number of fields, and in the same order, and the same number of records in each table and they can of course be matched on a common customer number.

How can I easily code for a comparison of all fields please?
Reporting could be all fields, highlighting non-matching fields, or preferably, just the field pairs with the non-matching data.

All suggestions gratefully received.

Cheers
AlanM

 

[Dennisk]

In this senario I would use two recordsets as it is possible to refere to a column by position without knowong the column name. ie

if rstTable1(1) <> rstTable(1) then
this would be embedded in a for next loop and the index would be used to refer to the columns

so

for j = 1 to n where n is the number of columns (if this changes over then use the table column count instead)

if rstTable1(j) <> rstTable(j) then
add the difference data to a temp table
endif

next j

 

[WayneRyan]

Alan,

You might want to take a peek at

http://www.access-programmers.co.uk/forums/showthread.php?p=587270#post587270

That could actually provide the beginnings of a report, with little effort.

Secondly, you should be able to make a fairly simple query to join the
two tables and report the differences using the Criteria section in
the query grid.

Just some thoughts,
Wayne

 

[AlanM]

Table Compare

Hi Wayne/DennisK

Many thanks for your replies, that has helped me.

I had a bit of a issue with moving to the next record in each table so that I was actually comparing what should be "matching"records, so I was wondering:

if, and there shouldn't be, but could be, more records in one table than the other,

if I linked these by a primary key, a movenext in one table would automatically = "movenext" in the linked table would it not. (I can separately compare the tables to find records in one table and not in the other, and report accordingly)

How would I create the link/key in VB?

Cheers
AlanM

 

[WayneRyan]

Alan,

I'd use queries rather than recordsets.

Get Duplicates:

Select B.PrimaryKey, Count(*)
From   Table1 As A Left Join Table2 As B On
       A.PrimaryKey = B.PrimaryKey
Group By B.PrimaryKey
Having count(*) > 1

Since removal/consolidation of any duplicates is a manual thing, I wouldn't
try to do it in VBA.

Wayne