identify the rows with a matched or unmatched field in multiple tables

[Will Fleenor]

At various times, I need to compare two or more EXCEL worksheets to identify
the rows with a designated field that matches as well as those that does not
match the fields in rows of multiple worksheets. I have been importing the
EXCEL worksheets into ACCESS to use the query function to identify such
rows. However, in creating the relationships between the tables, the
options offered are only to match fields of all tables or a single table.

The option to identify those rows with a field that does not match the
designated field of the other tables is not available. To work around this
limitation, I have been combining the designated field of all of the tables
and deleting the duplicated fields to create a master field table and
relating this master field table to all of the tables to identify the rows
with a matched or unmatched field.



The process to create this master field table can, at times, be tedious and
time consuming. Is there a more efficient function in ACCESS to identify
the rows with a matched or unmatched field in multiple tables, or could this
be done in EXCEL using macros? If so, please direct me to a website or
resource material that would allow us to improve the efficiency of this
process.



thanks, Will Fleenor

 

[John W. Vinson]

At various times, I need to compare two or more EXCEL worksheets to identify
the rows with a designated field that matches as well as those that does not
match the fields in rows of multiple worksheets. I have been importing the
EXCEL worksheets into ACCESS to use the query function to identify such
rows. However, in creating the relationships between the tables, the
options offered are only to match fields of all tables or a single table.

Huh!?

Relationships are *only* between pairs of tables. What option is this "to
match fields of all tables"?

The option to identify those rows with a field that does not match the
designated field of the other tables is not available.

Well... not in the Relationships window, no. But that's not a relationship!

You can create a Query using the "Unmatched Query Wizard", with or without a
relationship defined between the tables.

To work around this
limitation, I have been combining the designated field of all of the tables
and deleting the duplicated fields to create a master field table and
relating this master field table to all of the tables to identify the rows
with a matched or unmatched field.

I have no idea what you mean by a "master field table". Could you give an
example?

The process to create this master field table can, at times, be tedious and
time consuming. Is there a more efficient function in ACCESS to identify
the rows with a matched or unmatched field in multiple tables, or could this
be done in EXCEL using macros? If so, please direct me to a website or
resource material that would allow us to improve the efficiency of this
process.

If you could give at least some sample data - fieldnames, and a few records of
field content - someone should be able to propose the SQL view of a Query to
do this.

 

[Will Fleenor]

Your discussion was very helpful and the problem has been resolved. The
solutions is below:

I guess I was not clear in describing my problem. I was basically referring
to a possible workaround of the limitation of the "Join Type" in developing
relationships between multiple tables to create a query that would identify
the records with matched and unmatched designated field in such tables.

In any event, through the discussions you provided below, I have modified my
process to use the "Unmatched Query Wizard" function to build the "Master
Field Table", which is a table of the designated field that is common to all
of the tables. The designated field in the "Master Field Table" is joined
with the designated field of all the tables to create a query that would
identify the records with matched and unmatched designated field in the
tables.

I did not think of using the "Unmatched Query Wizard" function, since this
function only identifies unmatched fields between two tables, but I now
believe this will help and reduce the time to build the "Master Field
Table".

My explanation may still not be all that clear, but anyway, the discussions
you provided were very helpful.