Problem solve Get help with specific problems with your technologies, process and projects.

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

   FullId text
,  amount number);

   FullId text
,  amount number);

...then you can get what you need using:

,  Full1.amount AS Amount1
,  Full2.amount AS amount2
FROM Full1 INNER JOIN Full2 ON Full1.FullId = Full2.FullId
,  A.amount AS Amount1
,  NULL AS amount2
   FROM Full1 AS A
   WHERE NOT EXISTS (SELECT * FROM Full2 WHERE Full2.fullId = A.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.


Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.