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
|
|||
|
|||
Un matched query on more than one field
Gary, Thank you so much, it worked beautifully. I had
fiddled around in SQL but didn't really know what I was doing. Your reply helped me understand a bit about basic SQL syntax. Thanks again for taking the time to reply. Mary -----Original Message----- " I came to this board for assistance with essentially the same issue. Two tables each with three fields LN FN and ID ID is a primary key in both tables. There are records that are common to both tables, some records in both tables that are not in the other. I only want to work with the subset of records that is common to both tables based in the ID field. Once I have those records I only want to see the records where the ID's are the same but the LN field is different. In english, what I want to do is compare two tables and find those records where the ID's match but the LN's do not. Hi m, Did you try bringing both tables into a query, join on the 2 fields, go into SQL view where you might have something like: SELECT tbl1.*, tbl2.* FROM tbl1 INNER JOIN tbl2 ON tbl1.ID = tbl2.ID AND tbl1.LN = tbl2.LN; and change "=" to "" SELECT tbl1.*, tbl2.* FROM tbl1 INNER JOIN tbl2 ON tbl1.ID = tbl2.ID AND tbl1.LN tbl2.LN; Please respond back if I have misunderstood. Good luck, Gary Walter . |
#2
|
|||
|
|||
Un matched query on more than one field
In english, what I want to do is compare two tables and
find those records where the ID's match but the LN's do not. Hi Mary, There was one aspect to the solution below that I had not thought about until I just read another post from Michel. SELECT tbl1.*, tbl2.* FROM tbl1 INNER JOIN tbl2 ON tbl1.ID = tbl2.ID AND tbl1.LN tbl2.LN; It has to do with the final condition. If either (or both) LN could be Null, the above SQL would not return those records. LN Null = Null Null LN = Null Null Null = Null Michel's solution was to move this condition to the WHERE clause and use NZ: SELECT tbl1.*, tbl2.* FROM tbl1 INNER JOIN tbl2 ON tbl1.ID = tbl2.ID WHERE NZ(tbl1.LN tbl2.LN, -1); It will include where "Null=Null" though, but will catch where one is null, so they "do not match." If you do not want to return "Null=Null" case, I guess you could include second test in WHERE clause WHERE NZ(tbl1.LN tbl2.LN, -1) AND NOT (tbl1.LN IS NULL AND tbl2.LN IS NULL); One more thing learned. Thank you Michel. Gary Walter |
Thread Tools | |
Display Modes | |
|
|