If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
query
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; |
#2
|
|||
|
|||
query
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. "helen" wrote: 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; |
#3
|
|||
|
|||
query
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 .. "helen" wrote in message ... 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; |
Thread Tools | |
Display Modes | |
|
|