Combining a table WITH duplicates (merge with all values)

[kdorian]

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!