compare 2 tables

[Guest]

I have 2 tables, say Tb1 nad Tb2 with identical structure of 16 fields each.
Tb1 has 9843 unique records and tb2 has 9836 unique records (no key field,
uniqueness based on all fields together). How can I find records in Tb1 that
are not there in Tb2.

 

[Guest]

You can use the query wizard to create an unmatch records query, to find
which records are in table 1 but not in table 2

 

[Guest]

I had tried the 'Unmatched Query Wizard' but I dont think that would work in
this case because there is no primary key in these 2 tables and the Wizard
allows to match only one field- I tried matching more than one field from
both tables in Wizard but it retained only the last match selected by me. As
I had written in my initial post, these records are unique by all fields
together, say Fld1, Fld2, ....Fld16

 

[John Vinson]

I had tried the 'Unmatched Query Wizard' but I dont think that would work in
this case because there is no primary key in these 2 tables and the Wizard
allows to match only one field- I tried matching more than one field from
both tables in Wizard but it retained only the last match selected by me. As
I had written in my initial post, these records are unique by all fields
together, say Fld1, Fld2, ....Fld16

You can "roll your own" Unmatched query: create a Query by adding both
tables to the grid. Join Fld1 to Fld1, Fld2 to Fld2, Fld3 to Fld3 and
so on through all fields. Select all sixteen join lines and choose
Option 2 - "show all records in tbl1 and matching in tbl2".

Include all the fields from Tbl1 and only one (any *REQUIRED* field,
it can't be a valid Null in an existing record) from Tbl2, and put a
criterion on it of

IS NULL

The query will now show only those records from Tbl1 without a match.

John W. Vinson[MVP]

 

[Guest]

It worked but I realized after viewing the results that there are 3 fields
say Fld 7, Fld8, Fld9 which should not go into the equation, that is the
query results should not include those records from Tb1 which dont match
those in Tb2 just because values in these 3 fields are different in these 2
tables. For this, I deleted the joins between the tables on these 3 fields
but the query still gives the same results?

 

[John Vinson]

It worked but I realized after viewing the results that there are 3 fields
say Fld 7, Fld8, Fld9 which should not go into the equation, that is the
query results should not include those records from Tb1 which dont match
those in Tb2 just because values in these 3 fields are different in these 2
tables. For this, I deleted the joins between the tables on these 3 fields
but the query still gives the same results?

I really hesitate to ask but... perhaps you could open the query in
SQL view and post it here. If you're not joining on these three fields
(did you select and delete the join lines???) then they should not be
included in the results... but without knowing the actual content of
your tables, it's hard to say whether that would or would not change
the result.

John W. Vinson[MVP]

 

[Guest]

Well, I was able to do it by other means (deleting a few fields etc.) But I
wanted to know: if 2 tables are joined on several fields in design view, how
can one select all the joins in one go if I say wanted to make a change to
the type of join say from inner to outer

 

[John Vinson]

Well, I was able to do it by other means (deleting a few fields etc.) But I
wanted to know: if 2 tables are joined on several fields in design view, how
can one select all the joins in one go if I say wanted to make a change to
the type of join say from inner to outer

That would be nice, but I don't know any simple way to do it.

John W. Vinson[MVP]