How to return both matched and unmatched

[dev_gordie]

Hello Forum --
I have written a query that uses one table and one query. As you would expect, it returns records where there is a match. I want to be able to return all the records that match the records in the table AS WELL as the records from the table that have NO match in order to come up with the sums that are zero

As a word of explanation, this DB is for budgetary purposes. I need to generate reports that include sums of actual expenses as well as $0.00 when no actual expenses have yet occured. But, I'm not at all clear how to write the underlying query so as to get both.

Any help the forum can provide would be fantastic.

Thanks,
Gordon

 

[namliam]

use an outer join... Search the forum if you dont know what an Outer join is....

 

[dev_gordie]

Using Union Query in Another Query?

Thanks for your input. I read enough about Outer Joins (rather Full Outer Joins) to learn that Access db can't perform them, only Access objects.

I did read an article about Union Queries that helped me to simulate an Outer Join. So now I have an Inner Join Query and a Left Join Query hooked up in a Union Query and I have the matched and unmatched results that I needed.

Which brings me to another question. Is it possible to use a Union Query within another query? Now that I have the matched and unmatched results, I need to perform another query joining to a different table to result in matched and unmatched records based on a different set of data.

To be more specific, the Union Query I've just written shows me the matched and unmatched records for a set of expenses (appearing in query results) attributed to a table of project names, some of which have no expenses as of yet. Now I want to be able to perform another query on the results from the union query joined to the Employees table to get a list of those people who have turned in receipts and those who have not. Is this possible?

As always, all the advice and prodding is very much appreciated.

Gordon

 

[RV]

So now I have an Inner Join Query and a Left Join Query hooked up in a Union Query

You only need one query, the one with the left join.

RV

 

[dev_gordie]

RV, I'm scratching my head, but you're so right. I dumped the first half of the union query and got the same results.

Now I'll have to see what happens if I use these results in an additional query.

Thanks,
Gordon

 

[namliam]

?? Right and Left join = Outer join... Why cannot access perform them?

 

[boblarson]

While Access cannot perform a true FULL OUTER JOIN, it can perform an OUTER JOIN and if you first select the table you want all the records from and then add the table you want the unmatched, then you will be setting up a LEFT OUTER JOIN and that should get you what you want. If you added the other table in first and then the table you wanted all records out of, you would create a RIGHT OUTER JOIN.

 

[namliam]

While Access cannot perform a true FULL OUTER JOIN

= Carthesian join? ie. display each record from table 1 with each record from table 2... Yes it can....