query

[Guest]

I want to be able to take two tables I have and join them into 1 linking it
by last name and first name. The first name in table 1 is the actual first
name, but the first name in table 2 include first name and middle initial.

My query needs to be able to find all records that are the same from both
tables and all records that are not the same in both tables. I am trying to
use a UNION but i do not seem to get all the results I want. What am I doing
wrong? Should I not use a Union? I don't want to use a JOIN because I can't
get all the records from both tables.

SELECT [FIRM DIRECTORY].LASTNAME, [FIRM DIRECTORY].FIRSTNAME,
[TBLEMPMASTER].LASTNAME, [TBLEMPMASTER].FIRSTNAME,
[TBLEMPMASTER].[SOCIALSECURITY#]
FROM [FIRM DIRECTORY], TBLEMPMASTER
WHERE [FIRM DIRECTORY].LASTNAME=[TBLEMPMASTER].LASTNAME AND LEFT([FIRM
DIRECTORY].FIRSTNAME,2)=LEFT([TBLEMPMASTER].FIRSTNAME,2)
UNION
SELECT [FIRM DIRECTORY].LASTNAME, [FIRM DIRECTORY].FIRSTNAME,
[TBLEMPMASTER].LASTNAME, [TBLEMPMASTER].FIRSTNAME,
[TBLEMPMASTER].[SOCIALSECURITY#]
FROM [FIRM DIRECTORY], TBLEMPMASTER
ORDER BY [FIRM DIRECTORY].LASTNAME;

 

[Guest]

I am not an expert in Access but I can see that you have applied the
condition where the records do match. You are missing the condition where the
records do not match like
WHERE [FIRM DIRECTORY].LASTNAME=[TBLEMPMASTER].LASTNAME AND
LEFT([FIRMDIRECTORY].FIRSTNAME,2)<>LEFT([TBLEMPMASTER].FIRSTNAME,2)

The problem that I can tell is how you will know which records do match and
which ones do not? You need to create an expression where if the records
matches, it should be 1, and if the record do not match, then it is a 0.

I hope this might help.

 

[John Spencer]

So you basically want All the names from both tables. That would be a union
without any criteria.

Or do you want a list of names and a flag whether they are in table A only,
Table B only, or Both? Also use a NON-equi JOIN.

If the latter, then you need to use three queries in a union query.
Assumption: tbleMPMaster contains only the first name (no middle initial)

SELECT "BOTH" as MatchType,
[FIRM DIRECTORY].LASTNAME, [FIRM DIRECTORY].FIRSTNAME,
[TBLEMPMASTER].LASTNAME, [TBLEMPMASTER].FIRSTNAME,
[TBLEMPMASTER].[SOCIALSECURITY#]
FROM [FIRM DIRECTORY] INNER JOIN TBLEMPMASTER
ON [FIRM DIRECTORY].LASTNAME=[TBLEMPMASTER].LASTNAME AND
[FIRM DIRECTORY].FIRSTNAME LIKE NZ([TBLEMPMASTER].FIRSTNAME,"XZ") & "*"

UNION
SELECT "FIRM ONLY",
[FIRM DIRECTORY].LASTNAME, [FIRM DIRECTORY].FIRSTNAME,
[TBLEMPMASTER].LASTNAME, [TBLEMPMASTER].FIRSTNAME,
[TBLEMPMASTER].[SOCIALSECURITY#]
FROM [FIRM DIRECTORY] LEFT JOIN TBLEMPMASTER
ON [FIRM DIRECTORY].LASTNAME=[TBLEMPMASTER].LASTNAME AND
[FIRM DIRECTORY].FIRSTNAME LIKE NZ([TBLEMPMASTER].FIRSTNAME,"XZ") & "*"
WHERE TBLDMPMASTER.LASTNAME IS NULL

UNION
SELECT "MASTER ONLY",
[FIRM DIRECTORY].LASTNAME, [FIRM DIRECTORY].FIRSTNAME,
[TBLEMPMASTER].LASTNAME, [TBLEMPMASTER].FIRSTNAME,
[TBLEMPMASTER].[SOCIALSECURITY#]
FROM [FIRM DIRECTORY] RIGHT JOIN TBLEMPMASTER
ON [FIRM DIRECTORY].LASTNAME=[TBLEMPMASTER].LASTNAME AND
[FIRM DIRECTORY].FIRSTNAME LIKE NZ([TBLEMPMASTER].FIRSTNAME,"XZ") & "*"
WHERE [FIRM DIRECTORY].LASTNAME IS NULL

That last query may cause an error. If so, revert to your query that used
Left
SELECT "MASTER ONLY",
[FIRM DIRECTORY].LASTNAME, [FIRM DIRECTORY].FIRSTNAME,
[TBLEMPMASTER].LASTNAME, [TBLEMPMASTER].FIRSTNAME,
[TBLEMPMASTER].[SOCIALSECURITY#]
FROM [FIRM DIRECTORY] RIGHT JOIN TBLEMPMASTER
ON [TBLEMPMASTER].LASTNAME = [FIRM DIRECTORY].LASTNAME AND
LEFT([TBLEMPMASTER].FIRSTNAME,2) = Left([FIRM DIRECTORY].FIRSTNAME,2)
WHERE [FIRM DIRECTORY].LASTNAME IS NULL

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I want to be able to take two tables I have and join them into 1 linking it
by last name and first name. The first name in table 1 is the actual
first
name, but the first name in table 2 include first name and middle initial.

My query needs to be able to find all records that are the same from both
tables and all records that are not the same in both tables. I am trying
to
use a UNION but i do not seem to get all the results I want. What am I
doing
wrong? Should I not use a Union? I don't want to use a JOIN because I
can't
get all the records from both tables.

SELECT [FIRM DIRECTORY].LASTNAME, [FIRM DIRECTORY].FIRSTNAME,
[TBLEMPMASTER].LASTNAME, [TBLEMPMASTER].FIRSTNAME,
[TBLEMPMASTER].[SOCIALSECURITY#]
FROM [FIRM DIRECTORY], TBLEMPMASTER
WHERE [FIRM DIRECTORY].LASTNAME=[TBLEMPMASTER].LASTNAME AND LEFT([FIRM
DIRECTORY].FIRSTNAME,2)=LEFT([TBLEMPMASTER].FIRSTNAME,2)
UNION
SELECT [FIRM DIRECTORY].LASTNAME, [FIRM DIRECTORY].FIRSTNAME,
[TBLEMPMASTER].LASTNAME, [TBLEMPMASTER].FIRSTNAME,
[TBLEMPMASTER].[SOCIALSECURITY#]
FROM [FIRM DIRECTORY], TBLEMPMASTER
ORDER BY [FIRM DIRECTORY].LASTNAME;