Rank function in access

[shicmic]

hi all,
i have been trying to use this code so to rank Agents in table. I want to rank them as they offered product to the same user, but the agent who offered first will have rank 1, and the other is rank 2.

SELECT A.ID, A.Type, A.CDate, Count(*) AS Rank
FROM yourTable AS A INNER JOIN yourTable AS B ON A.Type = B.Type AND A.CDate >= B.CDate
GROUP BY A.ID, A.Type, A.CDate

but i have same error
you tried to execute a query that does not include the specified expression 'some filed that i don't need'as part of an aggregate function.

can anyone help me plase

 

[PHV]

What is the real SQL code ?
What is the real error message ?

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

 

[shicmic]

This is the code:
ime is name , MSISDN is customers phone number and pDatum means date of the order.
and the error i get is :
you tried to execute a query that does not include the specified expression 'dupe.pDatum'as part of an aggregate function.


SELECT q.Ime, q.MSISDN, q.pDatum, (SELECT COUNT(*) FROM qJoint as dupe WHERE dupe.MSISDN = q.MSISDN AND q.pDatum <= dupe.pDatum ORDER BY dupe.pDatum, dupe.MSISDN) as Proba
FROM qJoint as q;

thnak you in advance!

 

[PHV]

What about this ?

SELECT A.Ime, A.MSISDN, A.pDatum, Count(*) AS Proba
FROM qJoint AS A INNER JOIN qJoint AS B ON A.MSISDN = B.MSISDN AND A.pDatum >= B.pDatum
GROUP BY A.Ime, A.MSISDN, A.pDatum

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

 

[shicmic]

yes, this is finally working., thank you sooo much )