numbering row in a table by group

Hi,

i am using access and i want to rank my table by group of product
tblProd and i want

a,... a,1
b,... b,1
b,... b,2
c,... c,1
c,... c,2
c,... 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

There are three problems with your original query:

1. Access did not recognize the table alias "t" used in the subquery.

2. It would not "reset" the count.

3. You need at least one other field (or combination of fields) by which to
"rank" records, preferably uniquely.

Assuming you have such a field (it might typically be an AutoNumber field or
primary key, a Date/Time field, or some field specific to the kind of data
you're dealing with) and it is named "Your Other Field", you might try
something like this:

SELECT
t.PRODUIT,
(SELECT
Count (*)
FROM tblProd AS t1
WHERE
t1.PRODUIT = t.PRODUIT
AND
t1.[Your Other Field] = t.[Your Other Field]) AS nb
FROM tblProd AS t


"PF" wrote in message
. ..
Hi,

i am using access and i want to rank my table by group of product
tblProd and i want

a,... a,1
b,... b,1
b,... b,2
c,... c,1
c,... c,2
c,... 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

thanks

pf

"Brian Camire" a écrit dans le message de
...
There are three problems with your original query:

1. Access did not recognize the table alias "t" used in the subquery.

2. It would not "reset" the count.

3. You need at least one other field (or combination of fields) by which
to
"rank" records, preferably uniquely.

Assuming you have such a field (it might typically be an AutoNumber field
or
primary key, a Date/Time field, or some field specific to the kind of data
you're dealing with) and it is named "Your Other Field", you might try
something like this:

SELECT
t.PRODUIT,
(SELECT
Count (*)
FROM tblProd AS t1
WHERE
t1.PRODUIT = t.PRODUIT
AND
t1.[Your Other Field] = t.[Your Other Field]) AS nb
FROM tblProd AS t


"PF" wrote in message
. ..
Hi,

i am using access and i want to rank my table by group of product
tblProd and i want

a,... a,1
b,... b,1
b,... b,2
c,... c,1
c,... c,2
c,... 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

it works

thanks

"Brian Camire" a écrit dans le message de
...
There are three problems with your original query:

1. Access did not recognize the table alias "t" used in the subquery.

2. It would not "reset" the count.

3. You need at least one other field (or combination of fields) by which
to
"rank" records, preferably uniquely.

Assuming you have such a field (it might typically be an AutoNumber field
or
primary key, a Date/Time field, or some field specific to the kind of data
you're dealing with) and it is named "Your Other Field", you might try
something like this:

SELECT
t.PRODUIT,
(SELECT
Count (*)
FROM tblProd AS t1
WHERE
t1.PRODUIT = t.PRODUIT
AND
t1.[Your Other Field] = t.[Your Other Field]) AS nb
FROM tblProd AS t


"PF" wrote in message
. ..
Hi,

i am using access and i want to rank my table by group of product
tblProd and i want

a,... a,1
b,... b,1
b,... b,2
c,... c,1
c,... c,2
c,... 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