Compare Databases

[Ronald Cree]

Here's an easy one:

I want Database A to look at Database B and compare a field called "Record ID" which exists in both.

I then want to KEEP only the records in Database A where a match is found. I want to delete all the others. Database B needs to remain intact.

Thanks.

Edited October 11, 2005 by Guest

[mr_vodka]

I would probably create a temporay calc field in Table A, just for my own view so that I know what is going to be deleted.

You would have to related table A to B via the RecordID key.

Then in your calcfield, If (IsEmpty(TableB::RecordID); 1)

Do a find on calcfield for the value 1.

You will then have a list of what records exist in A that does not in B.

Double check your records, and then Delete Found set.

P.S. remember to always make backups just in case.

[Ronald Cree]

The field in Database B is not empty. It contains a record ID number.

For instance, I want Database A, which contains Record ID 809, to look at Database B, and if 809 exists in Database B, then Database A keeps it.

If there is no 809 in B, then A deletes it.

[Ender]

On a layout in DatabaseA, insert the RecordID field from DatabaseB. Perform a Find for >0 in that field. Then do Show Omitted from the Records menu. Now the found set should contain only those DatabaseA records with no corresponding DatabaseB record, so you can Delete the found set. This could be scripted.

[mr_vodka]

I know the field is Database B is not empty. When you create a relationship from Table A to Table B, you are looking for the relationship value to be empty. So again, in table A create your calc field pointing to the Relational field of TableB. If that relationship is empty flag it with 1.

If (IsEmpty(TableB::RecordID); 1)

or you can use case.

Case (IsEmpty(TableB::RecordID); 1)

However, you should use Ender's solution it is much quicker.

Edited October 11, 2005 by Guest