percentages, I know I did this wrong

[btothap]

HI,

I'm working on a DB to track customer sales traffic in a motorcycle shop. The success rate of the sales people is based on a 1-10 scale. Basically I wrote queries to tablulate the number of occurrences of each sales step in the DB, then wrote queries to add those totals together and figure percentages. I have about 8000 queries right now, and I KNOW there's a smarter way to do things.

I have to write numerous reports comparing the sales staff at different sales levels and with the method i've been using, the DB will be 50 megs before there is any data involved.

I'm pretty new to databases in general, I've used some sql in regards to writing cgi scripts that queried DBs, but i'm pretty ignorant.

I know i Probably haven't given nearly enough info, but any help or insight would be appreciated.

Thanks

Shane.

 

[ColinEssex]

You have 8000 queries??

To track sales in a motorcycle shop?

Col

 

[btothap]

its more like 60, but yeah its rediculous. It works, but I KNOW it can be done much much better.

Like I said, i'm calculating percentages of a certain field in the database per another field. So say for example I wanted to know the percentage of salespeople that hit sales step 5, I calculated the number that hit 5 in a query, then 6 onward to 10 in queries, wrote a query to tabluate, then one to calculate the percentage. I don't know VB script at all, and i barely know sql. I have perl experience, so scripting languages aren't totally foreign, but databases are.

So, If anyone has any advice as to a better direction, using a module i'm not familiar with perhaps, I'd be greatful for the help.

 

[Mile-O]

Are you using a different table to record each sales step? If so, your problem lies in there and you'll need to redesign the structure of your database to be able to manage it more efficiently.

What's your table structures - see if we can spot the problem that way?

 

[ColinEssex]

Mile-O beat me to it

 

[btothap]

actually I have one main table for the collected data.
the columns are ID(autonumber), salesperson, date, customer_name, phone_home, phone_cell, phone_work, email, source, new_used, unit_class, unit_make, manual_model, unit_model, unit_price, time_spent, sales_steps, comments.

I have several other tables, but they're mostly to fill dropdown boxes and the like. I've got a salespeople table, a table of the makes, the sales_steps, the source, the unit types and so on, but they are basically to aid in data entry.

When designing the intital table, I tried to logically break it down into smaller tables, but as each record is distinct, I couldn't come up with a way to do so that made any sense. I'm sure you could enlighten me.

 

[Vassago]

How many queries do you run through on one report?

 

[btothap]

Well one query actually feeds the report, but there are numerous that feed that one query. For the 10 sales steps, there are basically 3 queries for each step, one to figure the occurrences of that step, one to tally the occurrences of that step with those higher, and one to figure the percentage.

Currently the query that figures the tally for each step has the salesperson field in a separate form as a criteria, so on the "manage salesstaff" form if you choose chuck from the dropdown, you get reports for chuck. Portability becomes a major issue here though, because as it stands I'm going to need to write a million more queries for each new variant of the report, I think.

Again, I can see how i'm flawed, but i'm not smart enough at this stuff yet to see how to fix it.

 

[raskew]

There's a definite design issue here. Could you post a zip file of your primary table with some sample data?

 

[btothap]

here it is. Thanks for your help.

 

[Mile-O]

That example you posted is useless as the only table in it is one that is linked to an Excel spreadsheet.

If any of us tries to open it we can't, obviously, link into your spreadsheet.

Although, if your only table is a spreadsheet and that's the way you are getting data and using in your database - the problem could be in there as the way the two applications (Access and Excel) treat data is different, in a way.

 

[btothap]

I'm sorry, I exported the file incorrectly.

I was unfamiliar with access's file export conventions, Thats how green I am.

Here is a file that should work, and again I appreciate your taking the time to look at it.

 

[simongallop]

First of all are you replacing a customer record if they come in a second time eg: John Doe comes in 14 April and gets info on a bike. John Doe comes back 16 April and buys a bike. Is that 2 records or one record updated with the latest info? If 2 records how are you going to treat the data. Does the second record supercede the first or will you be taking both records into account in your stats?

What are the specific reports that you want to show? Is it just how many times they hit a certain level? Or are you taking duration into account? Over a specific time period eg a month / year / financial year?

 

[Vassago]

Well one query actually feeds the report, but there are numerous that feed that one query. For the 10 sales steps, there are basically 3 queries for each step, one to figure the occurrences of that step, one to tally the occurrences of that step with those higher, and one to figure the percentage.

Currently the query that figures the tally for each step has the salesperson field in a separate form as a criteria, so on the "manage salesstaff" form if you choose chuck from the dropdown, you get reports for chuck. Portability becomes a major issue here though, because as it stands I'm going to need to write a million more queries for each new variant of the report, I think.

Again, I can see how i'm flawed, but i'm not smart enough at this stuff yet to see how to fix it.

Are you saying that you have 30 queries running???

 

[btothap]

In response to Harry,

If a customer comes in multiple times, there will need to be new entries for each visit. Salespeople are compensated based on the sales step they are able to take a customer to, so if a customer comes in multiple times and gets a little farther down the process each time there would need to be records for each visit.

As far as the reporting goes, currently I'm working on reports to show total traffic by make, source (phone, floor, appointment), Most traffic by salesman highest ratios at various sales steps, Salesmans rank by store average, and so on. The time period will need to be incorporated, probably via user input. Most of the reports are no problem, but I need to realign my thinking when it comes to the percentage reports. A simple percentage doesn't really work in this case because if a sales person reaches step 6, he's also reached 1-5, so that has to be tabulated in to the totals for each of the preceeding steps. I'm sure there's a way to handle it with a few lines of code, but I don't know vb,

and in Response to Vassago, I do currently have 30 queries powering one report, you can imagine how fast it is...

Thanks again for your continued assistance.

-shane.

 

[simongallop]

How about summing the levels that the salesperson has got to and viewing that as a percentage of total sales? (Can also count the number of records per salesperson and get an average level per trade) eg:

SP1: Lvl5
SP1: Lvl3
SP1: Lvl3
SP1: Lvl7
SP2: Lvl9
SP2: Lvl1

Average over company: SP1: 18/28 SP2: 10/28

or

Average level reached SP1: 4.5 SP2: 5


You can also group your table by level so that you know that level 3 has been hit uniquely 6 times (ie not incorporating the levels above) and then you can summ these results eg:

Lvl1: 5
Lvl2: 3
Lvl3: 8
etc.

Report can then show:

Lvl1: 16
Lvl2: 11
Lvl3: 8

HTH

 

[btothap]

Those are good ideas. I've incorporated logic like that in the grand totals queries, which are quite a bit less complicated than the more salesperson, and sales step specific ones. Unfortunately a true average, i.e. 4.5 doesn't provide useful data, as the sales staff is compensated on whole sales steps.

Its the situations where, for example, I need to calculate the highest sit down (sales step 5) ratio for each salesperson. So basically I need the query to tabulate the total 5's (5+4+3+2+1) for each salesperson, get an average of totals for each salesperson, then compare it to store totals.

If i'm missing a way to extend the logic you've explained to achieve the results above, please let me know.

Thanks again.

 

[btothap]

I just realized that I spelled that out backwards,

its not 5 thru 1 that needs to be tabulated, its 5 thru 10

 

[simongallop]

To get a list of how many time a salesperson hits a certain level you will need 2 queries. The first strips the description off the level so that it just has the numbers and sums it be occurence. The second creates the running total. The output looks something like:

In Table:

SP1, Lvl2
SP1, Lvl6
SP2, Lvl1
SP3, Lvl2

OUTPUT:
SP1, Lvl1, 2
SP1, Lvl2, 2
SP1, Lvl3, 1
SP1, Lvl4, 1
SP1, Lvl5, 1
SP1, Lvl6, 1
SP2, Lvl1, 1
SP3, Lvl1, 1
SP3, Lvl2, 1

If that is what you require then:

1/ Create a query and paste in the following code (presuming that you have not changed the fields from the example)

SELECT log_data.salesperson, Val(Left([sales_steps],2)) AS step_num, Count(log_data.salesperson) AS Occ
FROM log_data
GROUP BY log_data.salesperson, Val(Left([sales_steps],2));

Save the query as: Sales_Num

2/ Create a query and paste in the following code:

SELECT Sales_Num.salesperson, 1 AS Step, Sum(Sales_Num.Occ) AS SumOfOcc
FROM Sales_Num
WHERE (((Sales_Num.step_num)>=1))
GROUP BY Sales_Num.salesperson, 1
UNION SELECT Sales_Num.salesperson, 2 AS Step, Sum(Sales_Num.Occ) AS SumOfOcc
FROM Sales_Num
WHERE (((Sales_Num.step_num)>=2))
GROUP BY Sales_Num.salesperson, 2
UNION SELECT Sales_Num.salesperson, 3 AS Step, Sum(Sales_Num.Occ) AS SumOfOcc
FROM Sales_Num
WHERE (((Sales_Num.step_num)>=3))
GROUP BY Sales_Num.salesperson, 3
UNION SELECT Sales_Num.salesperson, 4 AS Step, Sum(Sales_Num.Occ) AS SumOfOcc
FROM Sales_Num
WHERE (((Sales_Num.step_num)>=4))
GROUP BY Sales_Num.salesperson, 4
UNION SELECT Sales_Num.salesperson, 5 AS Step, Sum(Sales_Num.Occ) AS SumOfOcc
FROM Sales_Num
WHERE (((Sales_Num.step_num)>=5))
GROUP BY Sales_Num.salesperson, 5
UNION SELECT Sales_Num.salesperson, 6 AS Step, Sum(Sales_Num.Occ) AS SumOfOcc
FROM Sales_Num
WHERE (((Sales_Num.step_num)>=6))
GROUP BY Sales_Num.salesperson, 6
UNION SELECT Sales_Num.salesperson, 7 AS Step, Sum(Sales_Num.Occ) AS SumOfOcc
FROM Sales_Num
WHERE (((Sales_Num.step_num)>=7))
GROUP BY Sales_Num.salesperson, 7
UNION SELECT Sales_Num.salesperson, 8 AS Step, Sum(Sales_Num.Occ) AS SumOfOcc
FROM Sales_Num
WHERE (((Sales_Num.step_num)>=8))
GROUP BY Sales_Num.salesperson, 8
UNION SELECT Sales_Num.salesperson, 9 AS Step, Sum(Sales_Num.Occ) AS SumOfOcc
FROM Sales_Num
WHERE (((Sales_Num.step_num)>=9))
GROUP BY Sales_Num.salesperson, 9
UNION SELECT Sales_Num.salesperson, 10 AS Step, Sum(Sales_Num.Occ) AS SumOfOcc
FROM Sales_Num
WHERE (((Sales_Num.step_num)>=10))
GROUP BY Sales_Num.salesperson, 10;

Call the query whatever you want!

HTH

 

[btothap]

Thanks Harry, you rule.

That simplifies things a great deal and gives me a good place to start with future queries.

for the more pointed ones I found a workaround thats less elegant than yours, but i suppose the end of the line is all that matters.

SELECT DISTINCT dupestest.salesperson, Count(dupestest.sales_steps) AS wuR
FROM dupestest
WHERE (((dupestest.sales_steps)="6 - Writeup No Deposit" Or (dupestest.sales_steps)="results
7 - Writeup Deposit" Or (dupestest.sales_steps)="8 - Close" Or (dupestest.sales_steps)="9 - F&I" Or (dupestest.sales_steps)="10 - Delivery"))
GROUP BY dupestest.salesperson;

where dupestest is a query off of the main table with duplicates removed.

Thanks again to all for the help. Now I just have to figure out how to incorporate date ranges...

-shane.