Running total by group

  • Thread starter Thread starter Claire
  • Start date Start date
C

Claire

Hi all,
I'm not sure if this problem can be solved just with the report, or if I
have to rework the query as well.

I am counting the number of leads quoted in a week. I currently have a
query that filters out to leads that were quoted and gives the week ending
date of that quote. (I need to keep this query around anyway for a detailed
report every so often.) Off of this query I count the number of leads quoted
grouped by sales rep and by week ending date.

SELECT Count(QryQuoted_Leads.[LeadNo]) AS [CountOfLeadNo],
QryQuoted_Leads.[Full_Nickname], QryQuoted_Leads.Week_Ending
FROM QryQuoted_Leads
GROUP BY QryQuoted_Leads.[Full_Nickname], QryQuoted_Leads.Week_Ending;

My report is currently grouped by Week, and then lists the Sales Reps quotes
during that week. This has a sum of quoted leads for the week, and a running
total of leads quoted this year.

Here's my challenge: I want to have a running sum of quoted leads by Sales
Rep as well. The resulting report may look like:

1/1/09 Quotes YTD Quotes
Bob 6 6
Sue 3 3
Total 9 9

1/8/09
Bob 2 8
Sue 4 7
Joe 4 3
Total 10 18

I just found out about subreports (and I'm quite excited about them) and I
figure that may be helpful here, but I can't figure out how to do it.

Thanks for any help you can offer,
Claire
 
Hi Claire,

I think you will need some creative querying. In looking at your
example, I see the issue of what to do when a particular sales rep does not
have data for a particular week. To do YTD running totals, you will need to
include that sales rep, at least in all succeeding weeks. It may be possible
to use a crosstab query. I am only an amateur with those. Here is how I
might do it:

Create a query to get a unique listing of all of the week-end values:

qryWeeks:

SELECT DISTINCT QryQuoted_Leads.Week_Ending
FROM QryQuoted_Leads;

Create a query using that query to get unique listing of all of the
sales reps paired up with the week-end values on and after their first
week-end value:

qryWeeks Sales Reps:

SELECT DISTINCT A.Week_Ending, B.Full_Nickname
FROM qryWeeks AS A, QryQuoted_Leads AS B
WHERE (((A.Week_Ending)>=.[Week_Ending]));

Modify you existing query (assuming that you have named it "qryWeekly
Summary") so that it provides counts for all of the week-end / sales rep
pairings:

SELECT A.Week_Ending, A.Full_Nickname, Count(B.LeadNo) AS CountOfLeadNo
FROM [qryWeeks Sales Reps] AS A LEFT JOIN QryQuoted_Leads AS B ON
(A.Full_Nickname = B.Full_Nickname) AND (A.Week_Ending = B.Week_Ending)
GROUP BY A.Week_Ending, A.Full_Nickname;

Create a fourth query that does a self-join on your modified query that
provides the counts for the week-end / sales reps pairs and calculates the
YTD by summing up the current and all prior entries:

qryWeekly Summary with YTD Summary:

SELECT A.Week_Ending, A.Full_Nickname, A.CountOfLeadNo AS This_Week,
Sum(B.CountOfLeadNo) AS YTD
FROM [qryWeekly Summary] AS A INNER JOIN [qryWeekly Summary] AS B ON
A.Full_Nickname = B.Full_Nickname
WHERE (((B.Week_Ending)<=[A].[Week_Ending]))
GROUP BY A.Week_Ending, A.Full_Nickname, A.CountOfLeadNo;

Good Luck,

Clifford Bass

Claire said:
Hi all,
I'm not sure if this problem can be solved just with the report, or if I
have to rework the query as well.

I am counting the number of leads quoted in a week. I currently have a
query that filters out to leads that were quoted and gives the week ending
date of that quote. (I need to keep this query around anyway for a detailed
report every so often.) Off of this query I count the number of leads quoted
grouped by sales rep and by week ending date.

SELECT Count(QryQuoted_Leads.[LeadNo]) AS [CountOfLeadNo],
QryQuoted_Leads.[Full_Nickname], QryQuoted_Leads.Week_Ending
FROM QryQuoted_Leads
GROUP BY QryQuoted_Leads.[Full_Nickname], QryQuoted_Leads.Week_Ending;

My report is currently grouped by Week, and then lists the Sales Reps quotes
during that week. This has a sum of quoted leads for the week, and a running
total of leads quoted this year.

Here's my challenge: I want to have a running sum of quoted leads by Sales
Rep as well. The resulting report may look like:

1/1/09 Quotes YTD Quotes
Bob 6 6
Sue 3 3
Total 9 9

1/8/09
Bob 2 8
Sue 4 7
Joe 4 3
Total 10 18

I just found out about subreports (and I'm quite excited about them) and I
figure that may be helpful here, but I can't figure out how to do it.

Thanks for any help you can offer,
Claire
Click to expand...
 
Back
Top