Query on two tables

[adamstorch]

I have two tables with similar information, One table is information about Unix usernames and contains User Name, last name, first name, and middle initial.  The other table has information from Microsoft Exchange that includes last, first, and middle initial as well.  I want to query the two tables, and find out which records match perfectly, and which one's have differences.  thanks is advance

 

[Guest_imported]

do an inner join on all 3 fields this will show all the exact matches. to show the non matches use the find unmatched query wizard.<br>good luck!

 

[adamstorch]

I am unfamiliar to inner joins, i attempted to change the join properties in the query window but was unsuccessful, if you are going to respond (which i would REALLY appreciate) please try to give more explicit directions, because I think glo4 was very close to helping me, but could'nt figure it out exactly

 

[DiscoDuane]

Is there a relationship existing for those 2 tables<br>

 

[adamstorch]

there is'nt anything that really relates the two tables<br><br>the username table has the following information:<br>username<br>last name<br>first name<br>middle initial<br><br>the exchange table has the following information:<br>last name<br>first name<br>middle initial<br><br>the problem - the username has the correct last, first, and middle initial for all user names --- the exchange table contains correct information about some users, but I need a list of the users with incorrect information

 

[Guest_imported]

this will bring out where there is not an exact match in all 3 fields between the 2 tables<br><br>SELECT Username.[last name], Username.[First name], Username.initial, Exchange.lastname<br>FROM Username LEFT JOIN Exchange ON (Username.[last name] = Exchange.lastname) AND (Username.[First name] = Exchange.firstname) AND (Username.initial = Exchange.initial)<br>WHERE (((Exchange.lastname) Is Null));<br>you will need to replace text above to exactly match field names and table names

 

[adamstorch]

glo4, thanks for all your help, but where would I put the above code, into a query?

 

[adamstorch]

glo4, i put the code below into the query window in SQL view, and it did'nt work---it showed all UserInfo records including UserInfo last, first, middle, and the Exchange tables last name field<br><br>it did'nt exclude any records from the userinfo table, and the exchange field in the query (last name) was empty throughout the column<br><br>thank you so much for trying to help me<br><br><br>SELECT UserInfo.last, UserInfo.first, UserInfo.middle, ExchangeInfo.last<br>FROM UserInfo LEFT JOIN ExchangeInfo ON (UserInfo.middle = ExchangeInfo.middle) AND (UserInfo.first = ExchangeInfo.first) AND (UserInfo.last = ExchangeInfo.last)<br>WHERE (((ExchangeInfo.last) Is Null));

 

[Guest_imported]

Are you sure it brought in all of the records from the userinfo table? This query should bring in records where the 3 fields are not an exact match. The exchangeinfo field was supposed to be empty as there is no match in the other table. Do this! Change the parameter from is null to is not null and add exchangeinfo middle and initial to the query. Run the Query. This will show those that match. If it comes up blank then none of the records are an exact match. Let me know what you come up with.