Here's an interesting query request: Show me a list of all my Customers who do NOT have an Active Workers Compensation Policy. (This works equally well with any other type of Policy, BTW)
1. Create a query with these fields:
2. Check the Return Distinct Records box:
3. Open the Filter Wizard and add a filter:
4. Click OK to close the Filter Wizard, then Preview to run the query:
5. That’s probably a long list, and contains a lot of Customers you don’t want. Some ideas for further filtering the list:
(In each case, select the field first, then add the filter as shown below)
a. Only show Active Customers (take care because I don’t think this field is well regulated by the AMS interface)
b. Only show Commercial Lines Customers
AfW:
AMS360:
(For further information on the amazing Customer “Business with Agency” Checkbox Flag field, see the QueryPlus Mapping Document for AMS360, page 27)
c. To exclude Prospects and/or Suspects:
AfW:
AMS360:
(In case anyone is interested:
Yes, AMS changed the name of the field from ‘Status’ in AfW to ‘Type’ in AMS360
No, there is no ‘Status’ field in the Customer table in AMS360
Yes, this is the type of thing that makes database analysts grind their teeth in their sleep J)
d. To return only Customers who have at least ONE other ACTIVE Policy (but without specifying what type):
Use these filters (or any other Customer or Policy level field with filter) alone or in any combination to get the desired list.
Remember that each filter ADDS to all the other filters in restricting the number of records returned.
Be sure that the set of filters is correct for the desired list – and never neglect to verify and test your data!