Ranking Query Woes 2

[straybullet]

Clinging to Access 2003...
I have 2 versions of this 'sorta' working...

SELECT qryGroupedQuantities.strMaterial, qryGroupedQuantities.strSoldToPartyNum, qryGroupedQuantities.strSoldToPartyName, qryGroupedQuantities.SumOfCalcQuantity, (SELECT Count(*) AS HowMany
FROM qryGroupedQuantities AS Dupe
WHERE Dupe.strMaterial = qryGroupedQuantities.strMaterial
and (Dupe.SumOfCalcQuantity > qryGroupedQuantities.SumOfCalcQuantity
or(Dupe.SumOfCalcQuantity = qryGroupedQuantities.SumOfCalcQuantity and Dupe.id > qryGroupedQuantities.id))
) AS BeatenBy
FROM qryGroupedQuantities
ORDER BY qryGroupedQuantities.strMaterial, qryGroupedQuantities.SumOfCalcQuantity DESC;

gives perfect results - but takes hours to run (over 5 last time). Searching here at TT showed me the following (by PHV I believe in answer to a similar dilemma):

SELECT A.Date, A.Total, A.Type, 1+Count(B.Date) AS Rank
FROM qryTotal AS A LEFT JOIN qryTotal AS B ON A.Total<B.Total AND A.Date=B.Date
GROUP BY A.Date, A.Total, A.Type

That got me going in the right direction - so now I'm trying:

select qrygroupedquantities.strmaterial,qrygroupedquantities.strsoldtopartynum, qrygroupedquantities.strsoldtopartyname, qrygroupedquantities.sumofcalcquantity, Count(*)+1 as HowMany
from qrygroupedquantities left join qrygroupedquantities as Dupe on
Dupe.strMaterial = qryGroupedQuantities.strMaterial and (Dupe.SumOfCalcQuantity > qryGroupedQuantities.SumOfCalcQuantity or(Dupe.SumOfCalcQuantity = qryGroupedQuantities.SumOfCalcQuantity and Dupe.id > qryGroupedQuantities.id))
group BY qrygroupedquantities.strmaterial,qrygroupedquantities.strsoldtopartynum, qrygroupedquantities.strsoldtopartyname, qrygroupedquantities.sumofcalcquantity;

This runs in just a few minutes! The only issue is that for each strMaterial, the top and second ranked records both come up as 2 rather than 1 then 2.

Any suggestions? It's frustrating to be this close and not be able to see the way! Thanks in advance!


Let them hate - so long as they fear... Lucius Accius

 

[dhookom]

Aren't the quantities the same? If they are the same and you don't want them both to be 2 then you will need to define which should be 1 and which should be 2.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Replace Count(*) with Count(Dupe.strMaterial)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[straybullet]

You'd think that was the case, but it appears that the

or(Dupe.SumOfCalcQuantity = qryGroupedQuantities.SumOfCalcQuantity and Dupe.id > qryGroupedQuantities.id)

is doing it's job EXCEPT where it involves the top 2 records. ex:
strmaterial strsoldtopartynum sumofcalcquantity HowMany
1080A[tab][tab]30323323[tab][tab]1080[tab][tab]2
1080A[tab][tab]30324100[tab][tab]50[tab][tab]2
1080A[tab][tab]3032A522[tab][tab]50[tab][tab]3

In keeping with this strange behavior, materials with only a single record are 'ranked' as 2 as well.

Let them hate - so long as they fear... Lucius Accius

 

[PHV]

Again, replace this:
, Count([!]*[/!])+1 as HowMany
with this:
, Count([!]Dupe.strMaterial[/!])+1 as HowMany

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[straybullet]

Thank you both! PHV, I wasn't ignoring your initial posting of the fix, it must've gone through while I was getting my answer to dhookom together (in between dealing with work junk...)

Let them hate - so long as they fear... Lucius Accius