Ask the Expert

Query to combine all information from two tables in Access

I have two tables generated by queries from a main database. We saved these tables into a new Access database. The tables, "POSTED" and "UNPOSTED", both contain the following fields:
Project

    Requires Free Membership to View

    Login

Number, Task Number, Labor Code, Regular Hours

The POSTED table contains the hours spent per Project Number/Task Number/Labor Code that has been approved, while the UNPOSTED table contains the hours that are still pending and waiting to be approved. I want to create a query that combines the information and totals the Regular Hours for each record. The problem I'm facing stems from the fact that the possible relationship joins between tables are:

1: include only records that appear in both tables
2: include all records in table 1 but only those in table 2 that are present in table 1
3: include all records in table 2 but only those in table 1 that are present in table 2.

Assuming this scenario:
POSTED records: A,B,C,D
UNPOSTED records: B,C,D,E
where A,B,C,D,E are records containing unique Project Number/Task Number/Labor Code combinations.

If I set a query with 1) I obtain BCD, with 2) I obtain ABCD and with 3) I obtain BCDE. How can I set up a query to give me ABCDE? Is there a way to achieve this? Basically, there are instances where at a particular project phase, a certain combination of Project Number/Task Number/Labor Code (like "E" in my scenario) is not present in the POSTED table and thus is dropped and I don't want that to happen. I would really appreciate your help on this.

Thanks for your time!


What you are looking for is called a FULL OUTER JOIN. This JOIN type isn't implemented in Jet, the underlying database engine in Access, so it gets a bit ugly although it is possible.

As an example, if you have tables like:

CREATE TABLE Full1 (
   FullId text
,  amount number);

CREATE TABLE Full2 (
   FullId text
,  amount number);
...then you can get what you need using:
SELECT
   Full1.FullId
,  Full1.amount AS Amount1
,  Full2.amount AS amount2
FROM Full1 INNER JOIN Full2 ON Full1.FullId = Full2.FullId
UNION SELECT
   A.FullId
,  A.amount AS Amount1
,  NULL AS amount2
   FROM Full1 AS A
   WHERE NOT EXISTS (SELECT * FROM Full2 WHERE Full2.fullId = A.FullId)
UNION SELECT
   B.FullId
,  NULL AS amount1
,  B.amount AS Amount2
   FROM Full2 AS B
   WHERE NOT EXISTS (SELECT * FROM Full1 WHERE Full1.fullId = B.FullId);
This isn't exactly pretty, but it gets you what you need. If you switch to a database engine like MSDE, you can simply use a "passthrough" query to get a FULL OUTER JOIN using T-SQL and you?ll get exactly what you want.

For More Information

  • What do you think about this answer? E-mail the editors at editor@searchDatabase.com with your feedback.
  • The Best Database Design Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your database design -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

This was first published in May 2002

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top