Compare Tables (Vlookup?)

[Guest]

Hi:

Is there a way (via a Query) to do the following.

I have 2 tables......samples of the structure follow:

Table 1
The fields are "1st Approver" and "2nd Approver"

Table 2
The fields are "Username" and "Full Name"

I am looking for a way to determine the following:

ANY USERNAME in Table 2 does NOT exist ANYWHERE in Table 1's fields (ie "1st
Approver" and/or "2nd Approver").

Anyone have any thougths on how to do this???

Thank you

Warren Phillips

 

[Tom Ellison]

Dear Warren:

The table structure you use makes this slightly more difficult. I'll
discuss that with you if you're interested, just let me know.

The "normalization" problem can be synthetically removed. This would amount
to creating a simple list of names from the 2 columns (fields): "1st
Approver" and "2nd Approver".

SELECT [1st Approver] FROM [Table 1]
UNION ALL
SELECT [2nd Approver] FROM [Table 1]

You can use this to test for existence:

SELECT DISTINCT USERNAME FROM [Table 2] WHERE USERNAME NOT IN (

SELECT [1st Approver] FROM [Table 1]
UNION ALL
SELECT [2nd Approver] FROM [Table 1])

You may want to try this with and without the word ALL and see which is
fastest. It is possible either could be faster depending on the data you
have, specifically, the number of duplicated names in the two columns of
Table 1.

This will not match John R Smith with John R. Smith. Unless you have first
created a table of unique names, and then forced the users to choose only
from those names, then the name of a single person can likely be entered in
many different forms, making the matching you hope to do very challenging,
if not impossible.

Tom Ellison
Microsoft Access MVP

 

[Guest]

Thank you .......

So do I go into QUERY....and past the following (see below) into "FIELD" or
"CRITERIA"? I tried both...but am getting an error....ie..."The Syntax of
the subquery in this expresssion is incorrect".

Here is what I am pasting into QUERY...my actual TABLE name is "Approval List"

SELECT [1st Approver] FROM [Approval List] UNION ALL SELECT [2nd Approver]
FROM [Approval List]

Dear Warren:

The table structure you use makes this slightly more difficult. I'll
discuss that with you if you're interested, just let me know.

The "normalization" problem can be synthetically removed. This would amount
to creating a simple list of names from the 2 columns (fields): "1st
Approver" and "2nd Approver".

SELECT [1st Approver] FROM [Table 1]
UNION ALL
SELECT [2nd Approver] FROM [Table 1]

You can use this to test for existence:

SELECT DISTINCT USERNAME FROM [Table 2] WHERE USERNAME NOT IN (

SELECT [1st Approver] FROM [Table 1]
UNION ALL
SELECT [2nd Approver] FROM [Table 1])

You may want to try this with and without the word ALL and see which is
fastest. It is possible either could be faster depending on the data you
have, specifically, the number of duplicated names in the two columns of
Table 1.

This will not match John R Smith with John R. Smith. Unless you have first
created a table of unique names, and then forced the users to choose only
from those names, then the name of a single person can likely be entered in
many different forms, making the matching you hope to do very challenging,
if not impossible.

Tom Ellison
Microsoft Access MVP

Hi:

Is there a way (via a Query) to do the following.

I have 2 tables......samples of the structure follow:

Table 1
The fields are "1st Approver" and "2nd Approver"

Table 2
The fields are "Username" and "Full Name"

I am looking for a way to determine the following:

ANY USERNAME in Table 2 does NOT exist ANYWHERE in Table 1's fields (ie
"1st
Approver" and/or "2nd Approver").

Anyone have any thougths on how to do this???

Thank you

Warren Phillips

 

[John W. Vinson]

Thank you .......

So do I go into QUERY....and past the following (see below) into "FIELD" or
"CRITERIA"? I tried both...but am getting an error....ie..."The Syntax of
the subquery in this expresssion is incorrect".

Here is what I am pasting into QUERY...my actual TABLE name is "Approval List"

SELECT [1st Approver] FROM [Approval List] UNION ALL SELECT [2nd Approver]
FROM [Approval List]

This isn't either a Field nor a Criterion. It's the WHOLE QUERY.

Create a new query. Don't add any tables. Select View... SQL; you'll see a big
textbox with

SELECT;

in the upper left corner. Replace it with this entire SQL string, verbatim.

The query grid *is just a tool* to help you build SQL strings; the SQL is the
"real" query. And some queries - UNION queries for example - cannot be built
in the grid, and you *must* use the SQL window.

John W. Vinson [MVP]

 

[Tom Ellison]

Dear Warren:

Exactly as John says. This is a query in text form. It has two advantages:

- you can send and receive them in a newsgroup

- certain types of queries, or subqueries, cannot be written any other way.

So, this is "SQL", a text language (like most programming languages). The
"grid" is a graphic interface for creating and displaying SOME queries, but
not all. However, any query that exists has a text version as its basis.

Tom Ellison
Microsoft Access MVP

Thank you .......

So do I go into QUERY....and past the following (see below) into "FIELD"
or
"CRITERIA"? I tried both...but am getting an error....ie..."The Syntax of
the subquery in this expresssion is incorrect".

Here is what I am pasting into QUERY...my actual TABLE name is "Approval
List"

SELECT [1st Approver] FROM [Approval List] UNION ALL SELECT [2nd Approver]
FROM [Approval List]

This isn't either a Field nor a Criterion. It's the WHOLE QUERY.

Create a new query. Don't add any tables. Select View... SQL; you'll see a
big
textbox with

SELECT;

in the upper left corner. Replace it with this entire SQL string,
verbatim.

The query grid *is just a tool* to help you build SQL strings; the SQL is
the
"real" query. And some queries - UNION queries for example - cannot be
built
in the grid, and you *must* use the SQL window.

John W. Vinson [MVP]

 

[Guest]

This worked....Wahooooo..

Thank you both for all your time

Dear Warren:

Exactly as John says. This is a query in text form. It has two advantages:

- you can send and receive them in a newsgroup

- certain types of queries, or subqueries, cannot be written any other way.

So, this is "SQL", a text language (like most programming languages). The
"grid" is a graphic interface for creating and displaying SOME queries, but
not all. However, any query that exists has a text version as its basis.

Tom Ellison
Microsoft Access MVP

Thank you .......

So do I go into QUERY....and past the following (see below) into "FIELD"
or
"CRITERIA"? I tried both...but am getting an error....ie..."The Syntax of
the subquery in this expresssion is incorrect".

Here is what I am pasting into QUERY...my actual TABLE name is "Approval
List"

SELECT [1st Approver] FROM [Approval List] UNION ALL SELECT [2nd Approver]
FROM [Approval List]

This isn't either a Field nor a Criterion. It's the WHOLE QUERY.

Create a new query. Don't add any tables. Select View... SQL; you'll see a
big
textbox with

SELECT;

in the upper left corner. Replace it with this entire SQL string,
verbatim.

The query grid *is just a tool* to help you build SQL strings; the SQL is
the
"real" query. And some queries - UNION queries for example - cannot be
built
in the grid, and you *must* use the SQL window.

John W. Vinson [MVP]