Solved

Count of Clients in sales query

Posted on 2016-10-28
14
38 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +3
14 Comments
 
LVL 48

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 51

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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 51

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 48

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 48

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 37

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 48

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 48

Expert Comment

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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 …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

752 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