Solved

Count of Clients in sales query

Posted on 2016-10-28
14
32 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 47

Expert Comment

by:Dale Fye (Access MVP)
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 49

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 49

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 47

Expert Comment

by:Dale Fye (Access MVP)
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 47

Expert Comment

by:Dale Fye (Access MVP)
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 35

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 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 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 30

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 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 41868837
glad to help
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

816 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now