Compounds and Data Return Types

Have you ever created a compound query and found that the results were not what you expected? In a compound query, we can combine two queries by adding their results together, subtracting the results of one query from the other, or intersecting the results to find only the accounts or entries that exist in both queries.

The results of your compound sometimes fit and sometimes they don't. Sometimes it looks like there are pieces shoved where they don't belong, and all too often, pieces that are suddenly missing. The data return your queries utilize can leave you with these extra or missing results. There are some combinations that will always work, sometimes work, and then some that will never work.

Combinations that always work:
A + A = accounts from both queries
A – A = accounts in query 1 that are not in query 2
A x A = accounts that are only in both queries
JE + JE = journal entries from both queries
JE x JE = journal entries that are only in both queries
JE – A = journal entries from query 1 who’s accounts are not in query 2

Combinations that sometimes work:
JE – JE = journal entries in query 1 that are not in query 2 (only if both queries contain the same Journal Entries)
A + JE = accounts and/or journal entries from both queries, results would fluctuate dependent on your compounds data return type

Combinations that will never work:
JE x A = no results would be found, there are no journal entries in query 2 to match and intersect
A – JE = no entries or accounts would be removed, query 1 does not have the exact journal entries to match and then be subtracted
If you are unfamiliar with compounds or data return types, you may want to review these previous blog posts:

• 
  
• Data Return Types, Through a Magnifying Glass will discuss how a data return type will control the results of your query.
  
• Compound Queries in eTapestry will discuss what a compound query can do for you and how to build one.