straybullet
IS-IT--Management
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
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