I have a project at work, and I’m not sure how to go about it (well, in the past, the previous person just checked this all by hand, but I know there’s an easier way).
I have three tables, all of which have FirstName and LastName columns. I want to get a list of all the rows from Table3 that do not have a FirstName match in either Table1 or Table2.
Any tips on how to set that up in Access? I have done some (basic) mySQL queries in the past, but I’m having trouble figuring out Access.
Add all of the tables to the query in query view. Join Table 3 to Table 1 on first_name. Double click on the join line and switch the join type to to “Show all records from Table 3…”.***
Join Table 3 to Table 2 on first_name. Double click on the join line and switch it to “Show all records from Table 3…”***
Add the fields you want to see from Table 3 to the query plus first_name from both Table 1 and Table 2 (These will be used for criteria). Add the criteria “Is Null” to to Table_2.first_name. Add the criteria “Is Null” to to Table_1.first_name.
It may be helpful to run the query after you do each of these steps so you can see what each part is doing.
***Phrasing may not be exact.
You can also switch the query to SQL view and enter the code you want directly.
wouldn’t it be something like SQL (my syntax is rusty)
SELECT * FROM TABLE3 WHERE TABLE3.FIRSTNAME NOT IN ((SELECT TABLE2.FIRSTNAME FROM TABLE2) AND (SELECT TABLE1.FIRSTNAME FROM TABLE1));
Yours would work, with some tweaking.
I’d write the SQL this way.
SELECT *
FROM TABLE3 AS T3
LEFT OUTER JOIN TABLE1 AS T1 ON T3.FIRSTNAME = T1.FIRSTNAME
LEFT OUTER JOIN TABLE2 AS T2 ON T3.FIRSTNAME = T2.FIRSTNAME
WHERE T1.ID IS NULL AND T2.ID IS NULL
But Shagnasty’s got it using the GUI.