Sorting and Grouping Problem

[yamy]

Here's the basic info:
Table:User
Source,text-
New,number - default = 1,always is 1
Act,number - default 0, will change to 1 at some point
Table:Company
CoName,text
AcctMgr,text - has many companies with one or more users and one or more sources

Select Query =
Co.AcctMgr
U.Source -group by nothing for now (count won't include uAct=0, sum gives 'type mismatch error')
U.Act -group by Sum
U.new -group by Sum
Pctnz([u.act]))/nz([u.new])) Sort: Descending

Report = all the fields from query, grouped 1st by Pct and 2nd by AcctMgr.

The report should display
Percent/acctMgr/TotalNew/TotalAct/

I get:
100%/johnDoe/6/6
0%/johnDoe/1/0

I want:
85%/johnDoe/7/6

I have tried sorting the percent column at both the query level and the group level.

In either case it still splits acctMgrs and companies where ever the 'uAct' = 0.

Suggestions Welcome!

Thanks so much, and thanks to PaulBricker for pointing me in a better direction when I posed a variation on this question in Queries yesterday.

I have a monday-noon deadline, just to share some of my pressure with you!!

Thanks again,
yamy

 

[RickSpr]

The way you've expressed the query is confusing me. U.Act and U.New I figure you're Summing, but I can't tell what you're grouping by.

Could you please give the text from the SQL view of your query? Rick Sprague

 

[yamy]

Ah yes, the SQL statement - sorry, here it is

SELECT company.coAcctMgr, Count(user.uSource) AS CountOfuSource, Sum(user.uAct) AS SumOfuAct, Sum(user.uNewRequest) AS SumOfuNewRequest, (nz([uact])/nz([unewrequest])) AS pct
FROM company INNER JOIN [user] ON company.coID = user.coID
GROUP BY company.coAcctMgr, (nz([uact])/nz([unewrequest]))
ORDER BY (nz([uact])/nz([unewrequest])) DESC;

To review the case:

I have two tables: Company and User
A company has an account manager, and an account manager can have many companies.
A company has many users. Users have two states - Active represented first by a 0 and then by a 1 as state changes) or New Request (default to 1, always a 1, never changes)

The goal is to present a report that displays the account managers with the most active users of the total of new requests, in descending order.

The report should display
Percent/acctMgr/TotalNew/TotalAct/

HOWEVER ! The report splits acctMgrs and companies wherever the value of 'uAct' = 0.

I get:
100%/johnDoe/6/6
0%/johnDoe/1/0

I want:
85%/johnDoe/7/6

I have tried sorting the percent column at both the Query level and the Report group level.

Thanks for any and all suggestions!
Yamy

 

[RickSpr]

Ah! I think I see the problem. Your SELECT clause is calculating the percentage separately for each row (uact/unewrequest). That comes out as either 1/1 or 0/1--that is, either 100% or 0%. Since you're also grouping by the percentage, a change in the percentage causes a break in the grouping.

What you really want is to calculate the percent as Sum(uact)/Sum(unewrequest) - that is, on the ratio of the total active to total new requests for the account manager. Change the percentage calculation in the SELECT and ORDER BY clauses to Sum(nz([uact]))/Sum(nz([unewrequest])). Remove the calculation from the GROUP BY--you only want one row output for each account manager, so you should only group by account manager.
Rick Sprague

 

[yamy]

You are My new best friend!!
Thank you VERY much - this worked perfectly!
;-)
Yamy