Compare two records

[vmon]

I need to compare a record in tblA to the records in tblB to check for a change in the record. I need to compare all of the fields and if there is no record in tblB matching record in tblA write record in tblA to tblB. If a field is different I need to add one to a revision field in tblA and then write record to tblB. Is there an easy way to compare whole records in SQL or with a recordset? I have about 40 fields in the records and the find duplicates wizard only does 10 fields. I have not written very much SQL or code with recordset's.

thanks
vmon

 

[jebry]

Hi vmon!

Assuming the tables have primary keys, just do an update query and update each field when the primary keys match. You can run an unmatched query as a subquery to an append query to add the missing records.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[vmon]

I don't follow your recommendation. Here is more info.
The keys for the tables are tblA.ID_ITEM and tblB.ID_ITEM, tblB.REVISION. REVISION is in tblA but is not part if key. It would be a 1:M relationship but I have not set one up. If an ID_ITEM and REVISION are in both tables I need to check all of the fields and if there is one different then I need to +1 to the REVISION in tblA and then write record in tblA as new record to tblB. If there are no fields different then I have records that match exactly and I don't need to do anything. If ID_ITEM and REVISION are not in both tables then write record in tblA as new record in tblB.

Thanks,
vmon

 

[cmmrfrds]

I would break the problem down into 2 steps. The unmatched records can be an insert query. The matched (on the keys) records can be done through the fields collection either ADO or DAO.

Here are the basic steps.
1. select * from table1, table2 where key = key
2. Make an ADO client side cursor and retrieve query into recordset object.
3. check the field count in the fields collection.
4. divide field count by 2, which should be the index starting point for table2 fields.
at this point the result may be 40 which means there where 40 fields in the record of each table and it will be an equal number as per your specs.
5. setup loop comparing fields in each record
something like rs.fields(i).value = rs.fields(i+40).value
when you find a not = then do something like the insert.
6. would need to have 2 loops one until rs.EOF and the other on the fields collection.