Calculating Clients

[doozeracts]

Hi everyone-

This is my first post, hopefully someone can help! I'll try to be clear about my problem, as I'm not sure if I am making this harder than in should be, or if it really is complicated.

I'm developing a new FM solution for my company to help us track contacts.

We have a contact database that consists of a Company table and an Individuals table. Although one Company may have several employees, our office assigns one "account rep" per company. Therefore, I have a field called "Account Rep" in the Company table. This field is filled in based on a value list of our sales persons names (Rep A, Rep B, Rep C).

In the Individuals table I have a field called "Indv Type", also based on a value list, that assigns the type of contact it is ("client", "vendor", etc). I also have a field, "Indv Status" that designates if the contact is a "lead", "Active" or "Inactive".

So, for example:

Widgets-R-Us is a "Client" and a "Lead". The account rep is "Rep A". Widgets-R-Us has 4 employees. "Rep A", therefore, has 4 clients.

Things-N-Stuff is a "Client" and "Active". The account rep is "Rep B". Things-N-Stuff has 6 employees. "Rep B" has 6 clients.

I would like to be able to create reports that:

Count how many "clients" my company has total, and count how many "Clients" each "account rep" has. I would love to express account rep's share as a percentage.

(In the above example, My company has 10 total clients. Rep A has 40% of those clients, Rep B has 60%. Rep C has 0%.)

I also want to break down an account rep's total accounts between "lead" "active" and "inactive".

I was thinking I need to use the COUNT function, but am having trouble figuring it out.

[aldipalo]

Welcome to the forum.

First, I would suggest that you add a "Sales Rep" table to your database. Unless you have a fixed number of sales reps with zero turnover. With the sales rep table whenever you change sales reps you can just add the new rep by using a value list related to the sales rep table. This will also make your count easier which will make your reports easier.

HTH

Client_Test.zip

Edited October 23, 2007 by Guest
I added this test file as an example.