numbering row in a table by group

[PF]

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

 

[Brian Camire]

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]

thanks

pf

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

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

 

[PF]

it works

thanks

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

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