MS Access: Display ALL records form BOTH tables in a query

Factual Questions
1

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.

2

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.

3

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



SELECT  table1.ID1,
              table1.Cust,
              table1.Cont#,
              null,
              null
UNION
SELECT  null,
              null,
              null,
              table2.ID2,
              table2.Bill

Zev Steinhardt
4

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.

5

Ah, yes, so there is. I missed that.

Zev Steinhardt

6

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

7

OK, I have it.

First create a union query (thanks Zev):



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:


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#];
8

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?

9

Oops,

Mangetout beat me to it!
I think that will work!

Thanks!

10

: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

11

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.

12

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:



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



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

13

Cheers Zev, I may take you up on that one day…

14

:confused: 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:

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. :smiley: