ranking record with group

[PF]

Hi,

i am using access and i want to rank records by group

i have a product table tblProd with a field produit and associate data
a,...
b,...
b,...
c,...
c,...
c,...

and i want

a,1
b,1
b,2
c,1
c,2
c,3
Where the count is reset each time the product change

if i use
SELECT tblProd.PRODUIT, (select count (*) from tblProd t1 where t1.produit<
t.produit or (t1.produit = t.produit)) AS nb
FROM tblProd

This is not working. Acess dont recognize T1.
I supopose that it works in sql server or other.

Any advise?
regards

 

[M.L. Sco Scofield]

Take a look at the article at
http://support.microsoft.com/default.aspx?scid=kb;en-us;208946.

A lot of people have used this technique successfully.

Gook luck.

--

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Useful Metric Conversion #17 of 19: 1 billion billion picolos = 1 gigolo
Miscellaneous Access and VB "stuff" at www.ScoBiz.com