Salesman Performance

Z

zyus

I hv this data in my table

SalesID CustName SaleAmount NonPerformingTag
001 XXX 0.00 Y or N

How do i retrieve report or query for salesman who bringing in more than 50%
non performing account. Assuming out of 100 acct 50 acct turn out to be non
performing.

Appreciate your help
 
M

Michael Gramelspacher

I hv this data in my table

SalesID CustName SaleAmount NonPerformingTag
001 XXX 0.00 Y or N

How do i retrieve report or query for salesman who bringing in more than 50%
non performing account. Assuming out of 100 acct 50 acct turn out to be non
performing.

Appreciate your help
Click to expand...

SELECT Sales.SalesID,
SUM(Sales.SaleAmount) AS [Total Sales],
SUM(IIF([NonPerformingTag] = -1,SaleAmount,0))AS [Non-Performing Sales],
SUM(IIF([NonPerformingTag] = -1,SaleAmount,0)) /
SUM(Sales.SaleAmount) * 100 AS [Non-Performing Amount Percentage],
SUM(IIF([NonPerformingTag] = -1,1,0)) / COUNT(* ) * 100
AS [Non-Performing Count Percentage]
FROM Sales
GROUP BY Sales.SalesID
HAVING (((SUM(IIF([NonPerformingTag] = -1,[SaleAmount],0)) /
SUM([SaleAmount]) * 100) > 50));
 
M

Michael C

zyus said:
I hv this data in my table

SalesID CustName SaleAmount NonPerformingTag
001 XXX 0.00 Y or N

How do i retrieve report or query for salesman who bringing in more than
50%
non performing account. Assuming out of 100 acct 50 acct turn out to be
non
performing.

Appreciate your help
Click to expand...

I think your data structure needs some work. CustomerName should be in a
Customer table and NonPerforming should be a boolean or integer column.
SalesID should just be ID, it does not need the name of its parent
prepended.

Michael
 
Z

zyus

I use access basicly for data query and reporting. All of the data comes from
different system.
 
Top