# MS Access: Display ALL records form BOTH tables in a query **URL:** \ **Category:** Factual Questions\ **Created:** [December 19, 2002, 9:40pm UTC](https://boards.straightdope.com/t/ms-access-display-all-records-form-both-tables-in-a-query/143901 "2002-12-19T21:40:34Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![zoid](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zoid/32/336_2.png) [@zoid](https://boards.straightdope.com/u/zoid)\ **Post date:** [December 19, 2002, 9:40pm UTC](https://boards.straightdope.com/t/ms-access-display-all-records-form-both-tables-in-a-query/143901/1 "2002-12-19T21:40:34Z")
Is there a way to design a query in MS Access that will display the results of two (multiple) tables whether the records match or not? To clarify lets suppose I have table1 that looks like: ID1 - - CUST - - CONT# 1 - - - -Cust1 - - 123 2 - - - -Cust2 - - 234 and table2 which looks like: ID2 - - CONT# - - BILL 1 - - - - 234 - - - - $200 2 - - - - 345 - - - - $300 I would like to write a query to link these tables on CONT# with the results: ID1 - - CUST - - CONT# - - ID2 - - BILL 1 - - - -Cust1 - - 123 2 - - - -Cust2 - - 234 - - - - 1 - - - $200 - - - - - - - - - - - -345 - - - - 2 - - - $300 The idea is to display all data identifying both matched and unmatched records. ---
**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\ **Post date:** [December 19, 2002, 9:47pm UTC](https://boards.straightdope.com/t/ms-access-display-all-records-form-both-tables-in-a-query/143901/2 "2002-12-19T21:47:11Z")
If there’s potentially missing data from either side, that sounds like you want a full outer join, which Access can’t do. There may be a way around it by building up from subqueries; I’ll post back in a little while if I can work it out. ---
**Author:** ![zev\_steinhardt](https://avatars.discourse-cdn.com/v4/letter/z/97f17d/32.png) [@zev\_steinhardt](https://boards.straightdope.com/u/zev_steinhardt)\ **Post date:** [December 19, 2002, 9:50pm UTC](https://boards.straightdope.com/t/ms-access-display-all-records-form-both-tables-in-a-query/143901/3 "2002-12-19T21:50:28Z")
What you need is a union query. What you need is a union query. I’ll give you the SQL Server syntax. You will need to adjust for Access ```auto SELECT table1.ID1, table1.Cust, table1.Cont#, null, null UNION SELECT null, null, null, table2.ID2, table2.Bill Zev Steinhardt ``` ---
**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\ **Post date:** [December 19, 2002, 9:52pm UTC](https://boards.straightdope.com/t/ms-access-display-all-records-form-both-tables-in-a-query/143901/4 "2002-12-19T21:52:09Z")
I’m not sure that a union query will cut it; there’s a join there on the second row of the desired query result. ---
**Author:** ![zev\_steinhardt](https://avatars.discourse-cdn.com/v4/letter/z/97f17d/32.png) [@zev\_steinhardt](https://boards.straightdope.com/u/zev_steinhardt)\ **Post date:** [December 19, 2002, 9:52pm UTC](https://boards.straightdope.com/t/ms-access-display-all-records-form-both-tables-in-a-query/143901/5 "2002-12-19T21:52:39Z")
Ah, yes, so there is. I missed that. Zev Steinhardt ---
**Author:** ![zev\_steinhardt](https://avatars.discourse-cdn.com/v4/letter/z/97f17d/32.png) [@zev\_steinhardt](https://boards.straightdope.com/u/zev_steinhardt)\ **Post date:** [December 19, 2002, 10:01pm UTC](https://boards.straightdope.com/t/ms-access-display-all-records-form-both-tables-in-a-query/143901/6 "2002-12-19T22:01:02Z")
Too bad Access doesn’t support full outer joins or even temporary tables. Can you build a third table, populate it and query from it? Zev Steinhardt ---
**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\ **Post date:** [December 19, 2002, 10:05pm UTC](https://boards.straightdope.com/t/ms-access-display-all-records-form-both-tables-in-a-query/143901/7 "2002-12-19T22:05:32Z")
OK, I have it. First create a union query (thanks **Zev** ): ```auto SELECT Table1.[Cont#] FROM Table1 UNION SELECT Table2.[Cont#] FROM Table2 GROUP BY [Cont#]; ``` Save this as _Query1_ Then create a second query, using _Query1_ as the master table, joining outwards to the original two tables: ```auto SELECT Table1.ID1, Table1.Cust, Query1.[Cont#], Table2.ID2, Table2.Bill FROM (Query1 LEFT JOIN Table1 ON Query1.[Cont#] = Table1.[Cont#]) LEFT JOIN Table2 ON Query1.[Cont#] = Table2.[Cont#]; ``` ---
**Author:** ![zoid](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zoid/32/336_2.png) [@zoid](https://boards.straightdope.com/u/zoid)\ **Post date:** [December 19, 2002, 10:10pm UTC](https://boards.straightdope.com/t/ms-access-display-all-records-form-both-tables-in-a-query/143901/8 "2002-12-19T22:10:32Z")
I think z\_s may be on to someting with the union query but it needs some tweaking. What abut something along the lines of… SELECT ID1, CUST, CONT# FROM table1 LEFT JOIN table2 ON… UNION SELECT ID1, CUST, CONT# FROM table1 RIGHT JOIN table2 ON… basically making a union query from 2 select queries. What do you think? ---
**Author:** ![zoid](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zoid/32/336_2.png) [@zoid](https://boards.straightdope.com/u/zoid)\ **Post date:** [December 19, 2002, 10:12pm UTC](https://boards.straightdope.com/t/ms-access-display-all-records-form-both-tables-in-a-query/143901/9 "2002-12-19T22:12:53Z")
Oops, Mangetout beat me to it! I think that will work! Thanks! ---
**Author:** ![zev\_steinhardt](https://avatars.discourse-cdn.com/v4/letter/z/97f17d/32.png) [@zev\_steinhardt](https://boards.straightdope.com/u/zev_steinhardt)\ **Post date:** [December 19, 2002, 10:16pm UTC](https://boards.straightdope.com/t/ms-access-display-all-records-form-both-tables-in-a-query/143901/10 "2002-12-19T22:16:50Z")
:smack: “Save as Query1” That’s what I get for doing this on SQL Server and not in Access (yeah, yeah, I should’ve thought of a view)… Zev Steinhardt ---
**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\ **Post date:** [December 19, 2002, 10:21pm UTC](https://boards.straightdope.com/t/ms-access-display-all-records-form-both-tables-in-a-query/143901/11 "2002-12-19T22:21:33Z")
I really ought to get to grips with SQL Server someday - I just need a big project that will demand it; I know Access is widely regarded as little more than a toy (although perhaps not by you **Zev** ), It does suck badly on the multi-user aspect and is a bit clunky with very large tables, but I’ve found it remarkably powerful for small business needs. ---
**Author:** ![zev\_steinhardt](https://avatars.discourse-cdn.com/v4/letter/z/97f17d/32.png) [@zev\_steinhardt](https://boards.straightdope.com/u/zev_steinhardt)\ **Post date:** [December 19, 2002, 10:42pm UTC](https://boards.straightdope.com/t/ms-access-display-all-records-form-both-tables-in-a-query/143901/12 "2002-12-19T22:42:19Z")
When I first started learning about relational databases, I used Access and almost always used the Query Builder tool. Now that I’m used to SQL, I can barely use the tool and always write my queries in SQL. However, the Access syntax is clumsy. For example (at least through Access 2K, I didn’t check out 2002), you can’t alias table names. So, you have to type out: ```auto SELECT table1.column1, table2.column1, table1.column2 table3.column FROM table1, table2, table3 WHERE table1.column3 = table2.colum2 AND table2.column2 = ... ``` In T-SQL it’s much easier ```auto SELECT t1.column1, t2.column1, t1.column2 t3.column FROM table1 t1, table2 t2, table3 t3 WHERE t1.column3 = t2.colum2 AND t2.column2 = ... ``` If you ever need help with SQL Server **Mangetout** , feel free to just drop me an email. Zev Steinhardt ---
**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\ **Post date:** [December 19, 2002, 10:47pm UTC](https://boards.straightdope.com/t/ms-access-display-all-records-form-both-tables-in-a-query/143901/13 "2002-12-19T22:47:58Z")
Cheers **Zev** , I may take you up on that one day… ---
**Author:** ![SCSimmons](https://avatars.discourse-cdn.com/v4/letter/s/e495f1/32.png) [@SCSimmons](https://boards.straightdope.com/u/SCSimmons)\ **Post date:** [December 19, 2002, 10:56pm UTC](https://boards.straightdope.com/t/ms-access-display-all-records-form-both-tables-in-a-query/143901/14 "2002-12-19T22:56:34Z")
> [@](#): > > \*Originally posted by zev\_steinhardt \* > \*\* However, the Access syntax is clumsy. For example (at least through Access 2K, I didn’t check out 2002), you can’t alias table names. \*\* 😕 I don’t know jack about Access 2K or 2002, but you can sure as shootin’ alias table names in Access 97. Here’s a working query from one of my '97 databases: > [@](#): > > SELECT Trim([sup].[last\_name]) & ", " & Trim([sup].[first\_name]) AS Supname, sup.racf\_id, Trim([emp].[last\_name]) & ", " & Trim([emp].[first\_name]) AS Empname, emp.racf\_id > FROM informix\_employee AS emp INNER JOIN informix\_employee AS sup ON emp.supervisor = sup.employee\_ssn > WHERE (((emp.employee\_ssn)\>“001000000”) AND ((sup.employee\_ssn)\>“001000000”) AND ((emp.status\_id)=“A” Or (emp.status\_id)=“I”)) > ORDER BY Trim([sup].[last\_name]) & ", " & Trim([sup].[first\_name]), Trim([emp].[last\_name]) & ", " & Trim([emp].[first\_name]); The tables are linked from an Informix database, but I can’t imagine why it wouldn’t work the same way with local Access tables. Nor can I imagine why Microsoft would eliminate this functionality in upgrades. Of course, the syntax might be different, or Microsoft could be managed by idiots, or … well, I’ll stop there. 😃