# MS Access/database question

**URL:** <https://boards.straightdope.com/t/ms-access-database-question/644039>\
**Category:** Factual Questions\
**Created:** [December 14, 2012, 5:07pm UTC](https://boards.straightdope.com/t/ms-access-database-question/644039 "2012-12-14T17:07:45Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Eonwe](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/eonwe/32/240_2.png) [@Eonwe](https://boards.straightdope.com/u/Eonwe)\
**Post date:** [December 14, 2012, 5:07pm UTC](https://boards.straightdope.com/t/ms-access-database-question/644039/1 "2012-12-14T17:07:45Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Shagnasty](https://avatars.discourse-cdn.com/v4/letter/s/9dc877/32.png) [@Shagnasty](https://boards.straightdope.com/u/Shagnasty)\
**Post date:** [December 14, 2012, 5:16pm UTC](https://boards.straightdope.com/t/ms-access-database-question/644039/2 "2012-12-14T17:16:03Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![yoyodyne](https://avatars.discourse-cdn.com/v4/letter/y/a9a28c/32.png) [@yoyodyne](https://boards.straightdope.com/u/yoyodyne)\
**Post date:** [December 14, 2012, 5:49pm UTC](https://boards.straightdope.com/t/ms-access-database-question/644039/3 "2012-12-14T17:49:29Z")

</div>

You can also switch the query to SQL view and enter the code you want directly.

---

<div class="post-metadata">

**Author:** ![md2000](https://avatars.discourse-cdn.com/v4/letter/m/73ab20/32.png) [@md2000](https://boards.straightdope.com/u/md2000)\
**Post date:** [December 14, 2012, 9:18pm UTC](https://boards.straightdope.com/t/ms-access-database-question/644039/4 "2012-12-14T21:18:04Z")

</div>

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));

---

<div class="post-metadata">

**Author:** ![redtail23](https://avatars.discourse-cdn.com/v4/letter/r/3e96dc/32.png) [@redtail23](https://boards.straightdope.com/u/redtail23)\
**Post date:** [December 14, 2012, 9:29pm UTC](https://boards.straightdope.com/t/ms-access-database-question/644039/5 "2012-12-14T21:29:39Z")

</div>

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.