Report Level Left Joins and Union Joins

[sxschech]

I am an MS-Access User new to BO. I would like to know, without using Designer (as I am only an end user with no permission to alter/create universes) the best way to create the following: 1. At present I have a tab1 with results from Query 1; criteria A and tab2 with results from Query 2; criteria B. I would like to have tab3 be a combined report showing only those people that from tab2 that do not show up on tab1. (Additional tabs would be to show only those on tab1 and not on tab2 and a tab showing one record per person combining both tabs 1 and 2) For this expample, I am using the same universe, but a future request could require a different universe or be able to link in data from MS-Access. Thank you so much for assistance.

 

[blom0344]

Hello sxschech,

The most obvious way to handle your situation is to define a third and fourth set of dataproviders to give you the required result.

The third dataprovider is a correlated query (using the except operator).

Start a new dataprovider and rebuild the A query. Push on the ' combine query ' button and you get another tab in query design screen. Doubleclick on the U figure untill you get the - sign. Rebuild the B query.

The actual SQL statement runs like:

Select ...........................
Except
Select ...........................

So the effect is that you get the people from A query minus the people from B query.

Do the same but just the reverse for the fourth dataprovider. T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com