I have a data set. I need to be able to create two queries that each add a different value to a query calculation, then combine the queries so that I get BOTH results, like this:
Query 1
Name Office Calc
John North A
Mary South A
|
Query 2
Name Office Calc
John North B
Mary South B |
Combined I Want
Name Office Calc
John North A
John North B
Mary South A
Mary South B |
Combined I Actually Get
Name Office Calc
John North A
Mary South A |
I've tried both Joins and Unions. I've tried setting Outer Joins and Cross Product to Allowed, and Suppression to None. I've tried creating one field name with different values in each query, and two different field names where I use one from Query 1 and one from Query 2. I have set up a Join as 0-to-Many in both directions. I've set both Detail and Summary Aggregation to for every field in every query to None. I've tried setting up a Master-Detail relationship.
None of it has worked.
I thought this would be simple, but I cannot seem to make it work. What am I doing wrong?! This cannot be that hard!