Lets say I have two tables with names. I want to run a query or some sort of operation to find out what the non-matches are and have the non-matched names pulled into a third column. Is this possible?
Example of Table One: Example of Table Two:
Name: Name:
Joe McSnuffington Ted Macaroni
Larry Nully Joe McSnuffington
Mike Poopy Mike Poopy
Daniel Quebec Daniel Quebec
I’d like the finished product to look like:
Name from Table One Name from Table Two Missing Name
I hope I haven’t jumbled up what I’m asking here. Any help would be appreciated.
You need to do what is called an outer join. You can do that by selecting both column names in the query and then double clicking on the join line to select what you want. You will see the matches and blanks after that. You can do fancier things on top of that to make the results cleaner if you need to.
How do you delete the records in one table that include a match to records in another table?
For instance, Table1 has a few thousand names in the Name field. Table2 has a couple dozen names in the Name field. I wish to delete the records for all the names in Table1 that match any of the list in Table2.
An outer join in a delete query? I just tried that and got the error message: “Could not delete from specified tables.”
I know I’m missing something obvious somewhere. :smack:
Since you are interested in matching records (ie they are in BOTH tables), do a “normal” join (INNER), not an OUTER:
Start by doing a Select Query joining the two tables by linking the keys. Show the names field from table1 - you are now seeing only the records that have matching names in table2, ie names that are in both tables. Switch the query type to “Delete Query” (you get a new row in the grid: “Delete” . Drag the “*” from table 1 down into the query grid. It will indicate “From” in the delete row. Click the “!”.
The SQL will look something like:
DELETE table1.*, table1.EmplID, table1.FirstName, table1.LastName
FROM table1 INNER JOIN table2 ON table1.EmplID = table2.EmplID;