Solved

Count of Clients in sales query

Posted on 2016-10-28
14
31 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
 
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
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 34

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 119

Expert Comment

by:Rey Obrero
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

895 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

18 Experts available now in Live!

Get 1:1 Help Now