A QueryPlus user wanted to run a query based on this request:

"Give me all customers who have more than one Rep in the Account Service Groups".  

The first 2 entries in the Service Groups box on the main Customer screen (the Account Exec and Rep) are accessed through the QueryPlus fields ‘Customer Account Exec…..’ and ‘Customer Account Rep…..’, and are required by the AMS360 interface.

All of the other personnel for a Customer are added in the 360 interface through the Customer – Edit Customer – Name & Address – Service Groups – New screen, and their QueryPlus fields are:

1. Customer Additional Personnel Type of Employment

2. Customer Additional Personnel Last Name, First Name

3. Customer Additional Personnel Type of Business

4. Customer Additional Personnel Primary Indicator  

The Type of Business field can contain the following:

0 – All

1 – Personal Lines

2 – Commercial Lines

3 – Non Property & Casualty

5 – Life

6 – Health

7 – Financial Services

The Type of Employment field can be:  

P – Exec

R – Rep

T – Sales Center Rep

B – Broker

The Primary Indicator will be either Y or N based on the checkbox on the interface screen.

So to create the query in the original request above, pick these fields: (QueryPlus version 3.2 required)

Place a filter on the Type of Employment field:

To narrow the results to only Customers with an additional Rep, add a filter to the Last Name field:

Run this query and the dataset will contain only those Customers with at least 1 Rep in the Customer Additional Personnel table, and since there is always 1 Rep in the Customer table, these will be Customers with more than 1 Rep assigned to them.

Some variations:

• To see which Service Group the additional Reps are assigned to, add the Type of Business field to the query.  

• To only see Reps assigned to a particular Service Group, filter the Type of Business field accordingly.

• To only see active Customers, add the Customer Is Active field and filter it for ‘equals Y’.