Group on a totals field

[dharsh]

I have a report that is calculating account revenue for my sales reps. I am able to generate the report, group by the sales rep and then total their total account revenue. Now I want to group on the total field that I just calculated. How do I do that?

[Dal Jeanis]

I'm not sure I understand the request.

So if two different reps sell the same exact amount, say $123,272.50, then you want to add them together? Do you want to add them by tiers, where all the reps between $100K and $200K show up in one group, and the ones with $200K-$300K in a second group, and so on?

Or is there some other grouping level (like a region code) that you want them to be totaled on insteadl?

[dharsh]

No what I have is a group of sales reps that are responsible for managing 2-4 accounts. We invoice these accounts a flat monthly charge each month. My reports is grouped by sales rep and shows which accounts they manage, the monthly billing for each account and then a total monthly billing for all their accounts. Since the amount we invoice each account is different, I would like to group on the total amount so I can see which sales reps group of accounts invoice the most.

[Dal Jeanis]

Okay, so you already have a report that gives you a summary total by Sales Rep.

Are you saying that you'd like the same report to have the Sales Reps appear in order from Most to Least Total Billing Amount?

Or are you saying that you want a different report that just has the total dollar amount each Sales Rep manages, in order from highest amount to lowest amount?

[dharsh]

I would like the same report to have the Sales Reps appear in order from Most to Least Total Billing Amount?

[Dal Jeanis]

Okay, I'm blanking out here.

1) I started to suggest creating the simplest possible query that calculates the total sales for each rep, and joining that in on every record for each rep and sorting by that descending instead of by rep number.

2) It seems like there's an ORDER BY as well, but I don't believe the field that you want to sort by actually exists until after the report has already been generated. That will take a little research.

Number 1 will work, so I'd try that first.

If you don't know how to do that, then please post the important tables and fields that would be needed to figure out a sales person's monthly intake, along with the fields that those tables would be joined on/matched by.

[dharsh]

Okay, I'm blanking out here.

1) I started to suggest creating the simplest possible query that calculates the total sales for each rep, and joining that in on every record for each rep and sorting by that descending instead of by rep number.

2) It seems like there's an ORDER BY as well, but I don't believe the field that you want to sort by actually exists until after the report has already been generated. That will take a little research.

Number 1 will work, so I'd try that first.

If you don't know how to do that, then please post the important tables and fields that would be needed to figure out a sales person's monthly intake, along with the fields that those tables would be joined on/matched by.

[Dharsh]
I have a query that calculates the total sales for each rep. Now I want to sort based on the totals. Will what you suggested in #1 do that?

[Dal Jeanis]

Below, your own query that gets total sales is called Q1.

Query Q2 assigns each SalesRepID his SalesRepRank - the number of Sales Reps that each Rep beat, using SalesRepID as the tie-breaker. I used NZ to give zero to the guy with the lowest sales.

Q1 
SELECT SalesRepID, TotSalesAmt
(etc - however you got it);

Q2
SELECT 
   Q1A.SalesRepID, 
   NZ((SELECT Count(Q1B.SalesRepID)
       FROM Q1 AS Q1B  
       WHERE ((Q1B.TotSalesAmt < Q1A.TotSalesAmt) OR 
              ((Q1B.TotSalesAmt = Q1A.TotSalesAmt) AND 
               (Q1B.SalesRepID < Q1A.SalesRepID))),
      0) AS SalesRepRank
FROM Q1 as Q1A;

If you JOIN Q2 into the current query that makes your report, then you can sort (descending) and group on the SalesRepRank field rather than SalesRepID, and since Rank is unique to each Rep, you get the same report in the order you want.

[dharsh]

Below, your own query that gets total sales is called Q1.

Query Q2 assigns each SalesRepID his SalesRepRank - the number of Sales Reps that each Rep beat, using SalesRepID as the tie-breaker. I used NZ to give zero to the guy with the lowest sales.

Q1 
SELECT SalesRepID, TotSalesAmt
(etc - however you got it);

Q2
SELECT 
   Q1A.SalesRepID, 
   NZ((SELECT Count(Q1B.SalesRepID)
       FROM Q1 AS Q1B  
       WHERE ((Q1B.TotSalesAmt < Q1A.TotSalesAmt) OR 
              ((Q1B.TotSalesAmt = Q1A.TotSalesAmt) AND 
               (Q1B.SalesRepID < Q1A.SalesRepID))),
      0) AS SalesRepRank
FROM Q1 as Q1A;

If you JOIN Q2 into the current query that makes your report, then you can sort (descending) and group on the SalesRepRank field rather than SalesRepID, and since Rank is unique to each Rep, you get the same report in the order you want.

[dharsh]
I have never written code before. Where do I insert this code?

[Dal Jeanis]

Okay, different tack then.

Does your current report use a saved query to get its data?

Look at the report in design view.

There's a little box at the far top left of the report, just under the title.

Right-click that box, open the Property sheet, data tab, and see what the Record Source is.

If the record source is starts with "SELECT", then copy and paste the whole query into your next post.

If the record source is the name of a query, then open that query in design view, then switch to SQL view. (top left area of the design tab, dropdown box for different views, select SQL view), copy and paste the SQL into your next post.

In either of the above cases, in front of the SQL in your post, put the word CODE in square brackets -> [ ] After the SQL in your post, put the word /CODE in square brackets. That will preserve formatting (although there probably won't be any formatting to preserve at this particular point).

When I can see the SQL the system has already generated for you, I should be able to see whether it can be easily adapted to what you need.

[dharsh]

Below is my Query:

SELECT Project.Assigned, Project.[Account Name], Project.Project, Project.Level, Project.Revenue, Project.Active, Sum(Project.[Revenue]) AS TotalBDM FROM Project GROUP BY Project.Assigned, Project.[Account Name], Project.Project, Project.Level, Project.Revenue, Project.Active HAVING (((Project.Active)=True));

[dharsh]

SELECT Project.Assigned, Project.[Account Name], Project.Project, Project.Level, Project.Revenue, Project.Active, Sum(Project.Revenue) AS TotalBDM
FROM Project
GROUP BY Project.Assigned, Project.[Account Name], Project.Project, Project.Level, Project.Revenue, Project.Active
HAVING (((Project.Active)=True));

[Dal Jeanis]

1) Make a backup copy of your database.
2) Make a test copy of your database.
3) In the test copy, try this:

SELECT 
      P1.Assigned, 
      (SELECT Sum(P2.Revenue) 
             FROM Project AS P2 
             WHERE ((P2.Active=True) 
             AND (P2.Assigned = P1.Assigned))) As TotActRev,
      P1.[Account Name], 
      P1.Project, 
      P1.Level, 
      P1.Active, 
      Sum(P1.Revenue) AS TotalBDM
FROM Project AS P1
GROUP BY 
      TotActRev, 
      P1.Assigned, 
      P1.[Account Name], 
      P1.Project, 
      P1.Level, 
      P1.Active
HAVING (P1.Active = True) 
ORDER BY TotActRev DESC, P1.Assigned;

Had to look at it again. For some reason you are using Revenue as a Group By field, then trying to sum it. TotalBDM is always going to equal the group by Revenue.

Assumptions:
A) Revenue is the dollar amount you care about.
B) Assigned is the sales rep.
C) You want the total revenue from active projects for each assigned to be your sort order.

Given those assumptions, the subselect that uses the P2 alias for Project is giving you the total active revenue for each assigned. (TotActRev).
Everything else is pretty much as you had it, although I've killed the Revenue field, because it's a duplicate for TotBDM

I suspect that you are including some field in the GROUP BY because you don't know how to pass them otherwise. There is an aggregate function called FIRST that you can use for that, if they're all going to be the same within the GROUP BY. You may really want something like this, assuming that a project may have more than one active Level with revenue. If there is only one Level withactive revenue, then take Level out of the GROUP BY and use FIRST, same as Active. :

SELECT 
        P1.Assigned, 
        (   SELECT Sum(P2.Revenue) 
               FROM Project AS P2 
               WHERE ((P2.Active=True) 
               AND (P2.Assigned = P1.Assigned))
     ) As TotActRev,
        P1.[Account Name], 
        P1.Project, 
        P1.Level, 
        First(P1.Active) AS Active, 
        Sum(P1.Revenue) AS Revenue
FROM Project AS P1
WHERE (P1.Active = True)
GROUP BY 
        TotActRev, 
        P1.Assigned, 
        P1.[Account Name], 
        P1.Project, 
        P1.Level
ORDER BY 
        TotActRev DESC, 
        P1.Assigned,
        P1.[Account Name], 
        P1.Project, 
        P1.Level 
;

[dharsh]

I'm not sure I understand the request.

So if two different reps sell the same exact amount, say $123,272.50, then you want to add them together? Do you want to add them by tiers, where all the reps between $100K and $200K show up in one group, and the ones with $200K-$300K in a second group, and so on?

Or is there some other grouping level (like a region code) that you want them to be totaled on insteadl?

[Dharsh]
Each rep has 2 to 4 accounts. My query totals the revenue generated from each of their accounts and then provides me a total revenue for each rep. Now that I have the total revenue for each rep, I want to run a report that sorts based on total revenue.

[Dal Jeanis]

Please verify the following statements. If there may be more than one active level for a project, then

Project.Assigned,        (name or ID of sales rep)
Project.[Account Name],  (name or ID of account)     One Rep may have multiple accounts
Project.Project,         (name or ID of Project)     One account may have multiple projects
Project.Level,           (name or ID of level)       One project may have multiple levels
Project.Revenue,         (amount of revenue)         One level has only one revenue
Project.Active,          (yes or no)                 One level is either active or inactive

If the above is correct, then use the following:

SELECT 
        P1.Assigned, 
        (   SELECT Sum(P2.Revenue) 
               FROM Project AS P2 
               WHERE ((P2.Active=True) 
               AND (P2.Assigned = P1.Assigned))
     ) As TotActRev,
        P1.[Account Name], 
        P1.Project, 
        P1.Level, 
        First(P1.Active) AS Active, 
        Sum(P1.Revenue) AS Revenue
FROM Project AS P1
WHERE (P1.Active = True)
GROUP BY 
        TotActRev, 
        P1.Assigned, 
        P1.[Account Name], 
        P1.Project, 
        P1.Level
ORDER BY 
        TotActRev DESC, 
        P1.Assigned,
        P1.[Account Name], 
        P1.Project, 
        P1.Level 
;

On the other hand, if there is only one active level for each project:

Project.Assigned,        (name or ID of sales rep)
Project.[Account Name],  (name or ID of account)     One Rep may have multiple accounts
Project.Project,         (name or ID of Project)     One account may have multiple projects
Project.Level,           (name or ID of level)       One project has only one level
Project.Revenue,         (amount of revenue)         One project has only one revenue
Project.Active,          (yes or no)                 One project is either active or inactive

If the above is correct, then use the following:

SELECT 
        P1.Assigned, 
        (   SELECT Sum(P2.Revenue) 
               FROM Project AS P2 
               WHERE ((P2.Active=True) 
               AND (P2.Assigned = P1.Assigned))
     ) As TotActRev,
        P1.[Account Name], 
        P1.Project, 
        First(P1.Level) AS Level, 
        First(P1.Active) AS Active, 
        Sum(P1.Revenue) AS Revenue
FROM Project AS P1
WHERE (P1.Active = True)
GROUP BY 
        TotActRev, 
        P1.Assigned, 
        P1.[Account Name], 
        P1.Project
ORDER BY 
        TotActRev DESC, 
        P1.Assigned,
        P1.[Account Name], 
        P1.Project 
;