RowNumber/New sequence in a query

[Fabien]

Hello,

From this table
PROJECT PAYM_DATE
100 1/1/2000
200 1/1/2000
100 31/1/2000
300 1/1/2001

Is it possible, in a query, to get this result?
PROJECT PAYM_DATE SEQ
100 1/1/2000 1
100 31/1/2000 2
200 1/1/2000 1
300 1/1/2001 1

I alread looked at this solution http://www.lebans.com/rownumber.htm but it
is not what I am looking for.

In Oracle, this command is supprted :
ROW_NUMBER() OVER (PARTITION BY PROJECT ORDER BY PAYM_DATE) AS SEQ.

Does this king of command is available in MS Access?

The goal is to give to the new field 'SEQ' a new sequence starting from 1 to
n (n is the number of payments for a particular project ordering by date of
payment).

Thank you in advance.

Fabien

 

[Marshall Barton]

From this table
PROJECT PAYM_DATE
100 1/1/2000
200 1/1/2000
100 31/1/2000
300 1/1/2001

Is it possible, in a query, to get this result?
PROJECT PAYM_DATE SEQ
100 1/1/2000 1
100 31/1/2000 2
200 1/1/2000 1
300 1/1/2001 1

Use a subquery to calculate it:

SELECT T.Project, T.PAYM_DATE,
(SELECT Count(*)
FROM table As X
WHERE X.Project = T.Project
AND X.PAYM_DATE <= T.PAYM_DATE
) As Seq
FROM table As T

 

[Fabien]

Thank you very much. It is working fine.

One small remark: it is not possible to use this query and make a crosstab
query using this 3 columns.

Fabien

 

[Marshall Barton]

If that's something you need to do and the performance hit
is tolerable, then change the subquery to a DCount:

SELECT T.Project, T.PAYM_DATE,
DCount("*", "table", "Project =" & T.Project
& " AND PAYM_DATE <="
& Format(T.PAYM_DATE, "\#m\/d\/yyyy\#")
) As Seq
FROM table As T