How do I query on all accounts without an address?

At some point you may need to find all accounts in your database that do not have an address. For example, you may want to email those without an address to request that they provide that information so that you can update your records.
A. First we query on all accounts with an address:
 
  1. Click Queries in the menu bar
  2. Select a category that you want to store this query in
  3. Click New Query under Tasks
  4. Name the query Accounts with an address
  5. Set the Starting Criteria to Base/All Constituents
  6. Set the Data Return Type to Accounts
  7. Set the Criteria Matching to Match Each Criteria
  8. Under Criteria > Browse Fields select Account from the drop down
  9. Click Address Lines
  10. Check Field Has Any Value
  11. Click Save and View Queries (this will save the query and take you back into your category)

B. Now we will create a compound query to subtract out all of the accounts with an address (the query created above) from all constituent accounts in your database:
 
  1. Click New Compound Query under the tasks menu
  2. Name the query Accounts without an address
  3. Set the Data Return Type to Accounts
  4. Choose Subtract Query 2 from Query 1
  5. For Query 1 select Category: Base and Query: All Constituents
  6. For Query 2 select Accounts with an address, the query we created in Step A
  7. Click Save And Preview under Tasks

The compound query created in Step B will contain all accounts without an address.

This required two queries because if we try to create one query using the Address Lines field, and check off 'Field has no value', we may return some accounts that have multiple Personas with one missing an address. For example, if a constituent has both a Business and Personal persona, but only the Personal persona has an address, that constituent would be returned if we create one query with 'Field has no value' checked off within the Address Lines field, because they do have a Persona that does not have an address present. For that reason, it is best to create a compound to subtract out accounts that have an address on any of their personas, so that your results accurately produce accounts that do not have an address in any persona.

Environment

 eTapestry

Was this article helpful?