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