Here's one more data-scrubbing query: Identify all Customers who are coded as Prospects or Suspects in the AMS database, but who have one or more Active Policies.

For AMS360:

For AfW:

(Yes, the name of the field changed. Yes, that is annoying.)

Filters:

Customer Open Policy Count 'greater than 0' (zero)

Customer Status or Customer Type 'does not equal C'

Add Customer Account Rep Name and export the list to Excel to split it up and mail it to the various CSRs.

When I work with QueryPlus users in their AMS databases, I often see odd and/or conflicting data fields, and QueryPlus is great for finding data that needs to be fixed - I call these the 'data-scrubbing' queries. The query below is an excellent one for data-scrubbing, and there are many variations that are useful as well.

Start with this basic query:

Filters:

Customer is Active 'equals N'
Customer Open Policy Count 'greater than 0' (that's zero)

Run this query and you'll get a list of Customers with conflicting data - Inactive Customers shouldn't have Active Policies. The Customer Account Rep Name (that's the CSR) is included, so this query can be exported to Excel and each CSR can receive a list of Customers that need to be fixed.

For AMS360 users, the Customer Automatically Check Business with Agency Indicator field will show you whether a Customer's 'Business with Agency' checkboxes are being automatically updated. Add a filter for this field to identify Customers whose Business with Agency flags aren't being correctly updated.

Customer Type is different for AMS360 and AfW:

In AMS360, Customer Type can be C = Customer, P = Prospect or S = Suspect. Those choices in AfW are in the field called Customer Status.

In AfW, Customer Type can be C = Commercial or P = Personal Lines. In AMS360, this is handled in the Customer 'Business with Agency' Checkbox Flag.

Many QueryPlus users need a query like 'Show me all my Customers who have a Private Passenger Auto Policy with our Agency but who do NOT have a Homeowners Policy with us'. The query below returns this list, and can be used for any combination of two Policy Lines of Business as well, just by changing a couple of fields.

Filters:

Second Policy Effective Date 'before [today's date]'
Second Policy Expiration Date 'after [today's date]'
Second Policy Line of Business 'equals [the LOB that the Customer DOES have]' (use OR to include more than one LOB)
Second Policy Renewal Report Flag 'equals A or equals I'
Second Policy Status 'does not equal D'
Second Policy Sub Type 'does not equal S'
Policy Homeowners Count 'is null' (use appropriate field for the LOB that the Customer does NOT have)

Substitute any of the fields below in place of Policy Homewowners Count to filter for any specific LOB. If the LOB needed does not appear, contact the QueryPlus support team and we will quickly create the field you need. (there are effectively an unlimited number of possible LOBs, so we cannot create them all - the list will grow as users request them)

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:

Check the Return Distinct Records box:

Open the Filter Wizard and add a filter:

Click OK to close the Filter Wizard, then Preview to run the query:

a.       Only show Active Customers (take care because I don’t think this field is well regulated by the AMS interface)

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) 

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)

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!