Complex Totals Query--Calculates Percentages and Performs Crosstab

Sales Representative Percentages "Drops" Example

Also see: Total Queries: Showing Those With 0 Count Other Total Queries

This example calculates how many contacts are converted into sales--which are sold by the sales representative. There are apartments and subdivisions contacted as potential customers and entered into a database; when any of them are converted into sales, a field called "video_sold_by" has the value entered "Sales Representative" or "Customer Service." Otherwise, the value (as manipulated later in the query) is "no sale." What needs to happen is, within a certain subdivision, all such attempts need to be totaled, and all which are designated with the "sales representative" need to be totaled, which then enables you to calculate a percentage.

Here is some sample data from the table:

Yes, the Rep ID field links up to the sales representative's name in another table tblRepresentatives (representative_name) and the subdivision_id links up to the subdivision's name in another table tblSubdivisions (subdivision_name).

In this example, the subdivision "Butterfield Ranch" has total sales ATTEMPTS of 84, and 8 of them were converted to sales by the sales representatives.

This required two queries. The first one, named qrySalesRepSalesPercentages, looked like this:

You have to add up all the entries, and you also have to add up only those with the value "sales representative" in the "video_sold_by" field (and also, with that value in the "high_speed_internet_sold_by" fields and "digital_sold_by" fields). The fields that do this are TotSales and VidCnt, especially (for handling the video sales portion).

Here is the syntax of the TotSales field which adds up all the entries:

This configures those with no entry in "video_sold_by" (which weren't sold by anyone) with the value "nosale" in there, and counts it as well. (I'm not sure if I need to do that; I inherited from earlier configurations of queries I was working on towards this end. (I am wondering if I could reconfigure this to read TotSales: Count(([video_sold_by])) and it would still work.)

Here is the syntax of the expression that adds up ONLY those with the value "sales representative" in the "video_sold_by" field:

The query returns results like this (only Butterfield Ranch shown, though this query returns all records by a certain representative:

Again, all subdivisions are shown; this was only shortened for brievity.

For adding up sales rep. sales for "digital" and "high speed internet," you can actually place expressions for those in the same query near the end--following the same logic followed for VidCnt. Here are those expressions:

For digital
DigCnt: Sum(IIf([digital_sold_by]="Sales Representative",1,0))

The crosstab query totals everything--within each month, all values on VidCnt (for video) are added up (total sales rep sales), and all values from TotSales are added up within each month, and then they are divided to obtain the sales percentage.

Here is a screenshot of the crosstab query:

Created using Microsoft WORDPAD.