2 Tables Same Fields : Which Fields Are Different? 1

[Meander365]

Using Access 2000.

Hi,

I have two tables with the same fields. One used for current data, the other used for update requests.

What I want to do in VB is to highlight on a form (with the record source set to the update table) which fields are different by highlighting them in bold.

So, for example :
TABLE 1 (Currrent) : Field [Client Name] = "Robert"
TABLE 2 (Updates) : Field [Client Name] = "Bob"

And on the form the Client Name field is set to BOLD.

So I need to compare the two tables some how and more specifically, the fields within them.

Any ideas would be appreciated !!

Thanks!

 

[BNPMike]

Just join the tables together and select * with the criterion: Where T1.FieldA<>T2.FieldA or T1.FieldB<>T2.Fieldb or etc etc.


That's solved your problem. how you display that is unimportant.
mike.stephens@bnpparibas.com

 

[Meander365]

BNPMike - Thanks for that, but sort of got it working with this :-

Function CompareFields(FrmName, Fieldname As string, Form_Primary_ID As Integer)

Dim x As Boolean
Dim Table1LookUp
Dim Table2LookUp

Table1LookUp = DLookup((Fieldname), &quot;tbl_company&quot;, &quot;table1_ID = &quot; & Form_Primary_ID )
Table2LookUp = DLookup((Fieldname), &quot;tbl_company_requests&quot;, &quot;table2_ID = &quot; & Form_Primary_ID)

If Table1LookUp <> Table2Lookup Then
Forms(FrmName)(Fieldname).FontWeight = 900
Else
Forms(FrmName)(Fieldname).FontWeight = 100
End If

End Function

Then in the form with table2 as the recordsource, OnCurrent Event :-

Dim GoLookup as string
Dim Current_Primary_ID as integer

Current_Primary_ID = table2_ID

GoLookup = CompareFields(&quot;Formname&quot;, &quot;fieldname&quot;, Current_Primary_ID)

 

[nagyf]

Something must be common
otherwise if you have 9900 hits from the T1 and T2 tables with 100 equal records.
Where (T1.Code=T2.Code) and (T1.FieldA<>T2.FieldA or T1.FieldB<>T2.Fieldb or etc etc.)

...
The above solution does not handle the case when T1.Code=&quot;A&quot; exist but T2.Code=&quot;B&quot; does not.
I have a filecomp utility let call it &quot;fc&quot; from the DOS age

http://www.iki.kfki.hu/~nagyf/utils/fc.exe

. If you import the tables as text and run
&quot;fc /f T1.txt T2.Txt >T1-T2.txtí12&quot;
then you get a list like in the sample below.
-----------------------------------------------------
Sample output made by four windows in Personal Editor 2 (another good old DOS program)

http://www.iki.kfki.hu/~nagyf/utils/pe2.zip

:

[tt]
--- Top of file --- ¦--- Top of file ---
B,Bob,spelunker ¦B,Robert,spelunker
C,Claire,swimmer ¦C,Claire,swimmer
S,Steve,skier ¦T,Tom,sailor
T,Tom,sailor ¦--- Bottom of file ---
--- Bottom of file --- ¦
¦
¦
¦
¦
¦
j:\t1.txt ¦j:\t2.txt
¦
---------------------------------------+---------------------------------------
--- Top of file --- ¦--- Top of file ---
h:fc j:t1.txt j:t2.txt >j:t1-t2.txt ¦Reading file 'j:t1.txt'.
--- Bottom of file --- ¦Reading file 'j:t2.txt'.
¦ 1 B,Bob,spelunker
¦
¦*** 1 line(s) match. ***
¦
¦ 3 S,Steve,skier
¦
¦*** 1 line(s) match. ***
¦
j:\fc-t1-t2.bat 1 1 Replace¦j:\t1-t2.txt

¦
[/tt]
Full text of j:t1-t2.txt

[tt]
Reading file 'j:t1.txt'.
Reading file 'j:t2.txt'.
1 B,Bob,spelunker 1 B,Robert,spelunker

*** 1 line(s) match. ***

3 S,Steve,skier

*** 1 line(s) match. ***
[/tt]
¦
Ferenc Nagy
|\ /~ ~~|~~~ nagyf@alpha0.iki.kfki.hu Fax: (36-1)-392-2529 New!
| \ | | Institute of Isotope and Surface Chemistry
| \ | -+- 1525 Bp. POB 77. Tel. 36-1)-392-2550
| \| |

http://alpha0.iki.kfki.hu/~nagyf

`-' ' `-' &quot;The goal of the life is the struggle itself&quot;