Solved

Count of Clients in sales query

Posted on 2016-10-28
14
27 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 48

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 48

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

758 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

22 Experts available now in Live!

Get 1:1 Help Now