Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Sorting and Grouping Problem

Status
Not open for further replies.

yamy

Technical User
Nov 22, 2002
54
US
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
Pct:(nz([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
 
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
 
Ah yes, the SQL statement - sorry, here it is

Code:
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

 
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
 
You are My new best friend!!
Thank you VERY much - this worked perfectly!
;-)
Yamy
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top