Comparing two tables / subsets

[mredmond]

I have two tables or recordsets : A and B
Both are fairly large, with up to 1500 records in each.

Has anyone every written a procedure to determine if A = B or if A is a subset of B?

I'd appreciate your thoughts on how to approach this.
Thanks in advance.

 

[llkhoutx]

First of all, 1500 is a small number of records for Access. 60,000+ is pushing Access.

Do A & B have indices? Common indices? Are the fields of the two tables identical?

You could go through one table record by record, field by field, looking in the second table for duplicates.

If both tables have indices having identical values, you identify the unique indices in one table and then left query that list on the second table.

Of course, there's the check duplicate query wizard, but that's one field at a time.

A & B in the abstract doesn't give that much information. Do you know anything about A & B? Will every field in a table possibly duplicate every field in some record of the other table?

You've really asked a bare bones question. I can think of numerous solutions. Maybe the foregoing has helped you.

 

[WayneRyan]

Mike,

In A, but not in B:

Select *
From   TableA As A Left Join TableB As B On
          A.PrimaryKey1 = B.PrimaryKey1 And
          A.PrimaryKey2 = B.PrimaryKey2
Where  B.PrimaryKey1 Is Null And
       B.PrimaryKey2 Is Null

JET doesn't handle this way too well:

Select *
From   TableA
Where  PrimaryKey Not in
   (Select PrimaryKey
    From   TableB)

Reverse the above for in B, but not in A.

Wayne

 

[mredmond]

No the tables do not have indices and Yes the tables have identical structures.

Of course 1500 records is not large in the realm of databases, but in fact, my problem does not involve simply tables A & B; it involves A, B, C, D ... AA, AB, AC, etc. So to iterate through all the records one by one will be intensive, even on only 1500 records.

For those needing more info -

I am an auditor. I am auditing rights granted to users on a complex PeopleSoft system. In our version of PeopleSoft rights are granted by assigning Operator Classes to users. Some users have as many as 50 Operator Classes assigned to them. Each class contains assignments to menus, screens, processes, etc.

I am trying to iterate through the assigned Operator Classes to see if they are duplicated. It is easy to determine if a particular right is duplicated. It is not so easy to determine if all the rights granted in A are also granted by B, so there is no need to assign both A and B to the user, unless A or B also contain rights that are not contained in the other. Then you have to do the same with A & C, A & D ..., then B & C, B & D, etc.

As you all know, there are usually two ways to code a module - the elegant way or the brute force way. I am looking for the elegant way to do this, but am about to resign myself to a brute force method.

Thanks for the comments