Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Count of Clients in sales query

Posted on 2016-10-28
14
Medium Priority
?
45 Views
Last Modified: 2016-11-01
Hi
Using MS Access. I have a table, Sales_Income, which has 3 fields I wish to report on
  • Trans_Year
  • Client_Ref
  • Earnings

I'd like to get a row per year, So my query is as follows:
SELECT Sales_Income.Tran_Year, Count(Sales_Income.Client_Ref) AS CountOfClient_Ref, Sum(Sales_Income.Earnings) AS SumOfEarnings
FROM Sales_Income
GROUP BY Sales_Income.Tran_Year;

Open in new window


However, the Count of ClientRef is actually giving me the number of transactions. For example, if there are 3000 transactions in a year,the count of client is showing 3000.
How do I get the count of Clients?
Thanks
0
Comment
Question by:jdhackett
  • 5
  • 4
  • 2
  • +3
14 Comments
 
LVL 49

Expert Comment

by:Dale Fye
ID: 41863871
SELECT T.Tran_Year, Count(T.Client_Ref) as Clients
FROM (
SELECT DISTINCT Tran_Year, Client_Ref
FROM Sales_Income
) as T
GROUP BY T.Tran_Year
0
 
LVL 53

Expert Comment

by:Rgonzo1971
ID: 41863876
Hi,

pls try

select Tran_Year, Count(Client_Ref), Sum(Earnings) from (
SELECT Sales_Income.Tran_Year, SalesIncome.Client_Ref, Sum(SalesIncome.Earnings) AS Earnings FROM SalesIncome
GROUP BY SalesIncome.Tran_Year, SalesIncome.Client_Ref)
 group by Tran_Year;

Open in new window

Regards
0
 
LVL 1

Author Comment

by:jdhackett
ID: 41863915
Dale - that worked, but the Earnings are missing from the query.
Also, it turns out I need to add PolRef as well. Each client can have several policies, and I need a count of those as well.
I tried adding the Earnings as below, but now the Client Count is incorrect. And I can't figure out how to add PolRef!



SELECT T.Tran_Year, Sum(T.Earnings) AS SumEarnings, Count(T.Client_Ref) AS Clients
FROM (SELECT DISTINCT Tran_Year, Client_Ref, Earnings
 FROM Sales_Income
 )  AS T
GROUP BY T.Tran_Year;
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 53

Expert Comment

by:Rgonzo1971
ID: 41863950
correct code
select T.Tran_Year, Count(T.Client_Ref), Sum(T.Earnings) from (
SELECT Sales_Income.Tran_Year, Sales_Income.Client_Ref, Sum(Sales_Income.Earnings) AS Earnings FROM Sales_Income
GROUP BY Sales_Income.Tran_Year, Sales_Income.Client_Ref) as T
GROUP BY T.Tran_Year;

Open in new window

0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 41863953
So, how do you want to add the policies? Do you want a count of the number of policies per customer?

YOu might try:

SELECT T.Tran_Year, T.Client_Ref, Count(T.PolRef) as ClientPolicies, Sum(T.ClientPolicyEarnings) as ClientEarnings
FROM (
SELECT Tran_Year, Client_Ref, PolRef , Sum(Earnings) as ClientPolicyEarnings
FROM Sales_Income
GROUP Tran_Year, Client_Ref, PolRef
) as T
GROUP BY T.Tran_Year, T.Client_Ref
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 41863958
And then, if you need
Tran_Year, ClientCount, PolicyCount, TotalEarnings, that would look like:

SELECT T1.Tran_Year, Count(T1.Client_Ref) as Clients, Sum(T1.ClientPolicies) as TotPolicies,
SUM(ClientEarnings) as TotEarnings
FROM (
SELECT T.Tran_Year, T.Client_Ref, Count(T.PolRef) as ClientPolicies, Sum(T.ClientPolicyEarnings) as ClientEarnings
FROM (
SELECT Tran_Year, Client_Ref, PolRef , Sum(Earnings) as ClientPolicyEarnings
FROM Sales_Income
GROUP Tran_Year, Client_Ref, PolRef
) as T
GROUP BY T.Tran_Year, T.Client_Ref
) as T1
GROUP BY T1.Tran_Year
0
 
LVL 1

Author Comment

by:jdhackett
ID: 41864023
I want the total number of policies for the year.
Tried those two you posted, but getting a message "Syntax Error in Group By clause"

Thanks
0
 
LVL 40

Expert Comment

by:PatHartman
ID: 41864123
The Count(somename) function counts the rows returned in the recordset where SomeName is not null.  So, using multiple Count(SomeName) functions in the same query almost always results in all counts returning the same value.  You would only see differences if some columns contained nulls.

SQL Server has a feature that allows you to count distinct which is what you are trying to do.  To simulate this in an Access query requires a subquery.  This is necessary because each query supports only a single level of aggregation and you are looking for multiple levels.

I would suggest creating independent count queries for each set you want to count.  Then you can join the queries later for export.  

If these counts are destined for a report, organize the report as a main report with subreports and that will allow you to develop individual counts.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41864429
try this


SELECT A.Tran_Year, Count(A.Client_Ref) AS CountOfClient_Ref, Sum(A.SumOfEarnings) AS TotalEarnings
FROM (SELECT Sales_Income.Tran_Year, Sales_Income.Client_Ref, Sum(Sales_Income.Earnings) AS SumOfEarnings
FROM Sales_Income
GROUP BY Sales_Income.Tran_Year, Sales_Income.Client_Ref
)  AS A
GROUP BY A.Tran_Year;
0
 
LVL 49

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 41864494
Since there were multiple GroupBy clauses, it would be nice to know where the error is occurring.

Start with:
SELECT Tran_Year, Client_Ref, PolRef , Sum(Earnings) as ClientPolicyEarnings
FROM Sales_Income
GROUP Tran_Year, Client_Ref, PolRef

Open in new window

That should give you the earnings for each year, client, clientpolicy (PolRef). Then add the next piece:
SELECT T.Tran_Year, T.Client_Ref, Count(T.PolRef) as ClientPolicies, Sum(T.ClientPolicyEarnings) as ClientEarnings
FROM (
SELECT Tran_Year, Client_Ref, PolRef , Sum(Earnings) as ClientPolicyEarnings
FROM Sales_Income
GROUP Tran_Year, Client_Ref, PolRef
) as T
GROUP BY T.Tran_Year, T.Client_Ref 

Open in new window

This should give you the number of policies for each Tran_Year, Client_Ref combination , with the number of policies per client per year, and the total client earnings for that year.  Finally, the full query:
SELECT T1.Tran_Year, Count(T1.Client_Ref) as Clients, Sum(T1.ClientPolicies) as TotPolicies,
SUM(T1.ClientEarnings) as TotEarnings
FROM (
SELECT T.Tran_Year, T.Client_Ref, Count(T.PolRef) as ClientPolicies, Sum(T.ClientPolicyEarnings) as ClientEarnings
FROM (
SELECT Tran_Year, Client_Ref, PolRef , Sum(Earnings) as ClientPolicyEarnings
FROM Sales_Income
GROUP Tran_Year, Client_Ref, PolRef
) as T
GROUP BY T.Tran_Year, T.Client_Ref
) as T1
GROUP BY T1.Tran_Year 

Open in new window

Which should give you the Year, # of clients for the year, total number of client policies, and the total earnings for that year.
0
 
LVL 31

Expert Comment

by:hnasr
ID: 41864538
Is the data as follows? where:
y: Trans_year
c:  Client_Ref
t:: Transaction number
e: Earnings
nc: sum of clients

y    c  t   e
1   1   1 10
1   1   2 11
1   2   1 12
2   1   1 10
2   2   1 10
2   2   2 10
2   3   1 10

Requied Output
y  nc  sumEearn
1  2   32
2  3   40
0
 
LVL 1

Author Comment

by:jdhackett
ID: 41868653
Thanks Dale.
There was a missing "BY" in the GROUP Tran_Year, Client_Ref, PolRef code you posted. It worked fine once I fixed that.

SELECT T1.Tran_Year, Count(T1.Client_Ref) as Clients, Sum(T1.ClientPolicies) as TotPolicies,
SUM(T1.ClientEarnings) as TotEarnings
FROM (
SELECT T.Tran_Year, T.Client_Ref, Count(T.PolRef) as ClientPolicies, Sum(T.ClientPolicyEarnings) as ClientEarnings
FROM (
SELECT Tran_Year, Client_Ref, PolRef , Sum(Earnings) as ClientPolicyEarnings
FROM Sales_Income
GROUP BY Tran_Year, Client_Ref, PolRef
) as T
GROUP BY T.Tran_Year, T.Client_Ref
) as T1
GROUP BY T1.Tran_Year 

Open in new window

0
 
LVL 1

Author Closing Comment

by:jdhackett
ID: 41868654
Thanks!
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 41868837
glad to help
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question