Dear Jeremy:
SELECT Index, [Revised Event Code for PA SP Liab & Property],
[DOL Start], [DOL End], [DOL Process],
[ELGAR Event Name (10 NANANM)], [Group Cat Code PT & Clash],
[CAT Memo], [CAT Intervene], [CAT Stlmnt], [Process Seq],
[Print Memo], [Category Code], [Group Cat Code Flag],
(SELECT COUNT(*) FROM [20041022_Event Import 22Oct04] T1
WHERE T1.[DOL Process] = T.[DOL Process]
AND T1.[Process Seq] > T.[Process Seq]) AS Rank
INTO EventTest1
FROM [20041022_Event Import 22Oct04] T;
There are some details that arise about the above. First, if you want
the rank to start from 1 rather than from 0, add one to it. That is,
change this line:
(SELECT COUNT(*) FROM [20041022_Event Import 22Oct04] T1
to
(SELECT COUNT(*) + 1 FROM [20041022_Event Import 22Oct04] T1
Also, I did not ask, and you did not say, which way you want the
ranking to run. I coded this so the ranking would run from 0 (or 1)
for the greatest [Process Seq] value, becoming greater for lower
values of [Process Seq]. To reverse this, change:
AND T1.[Process Seq] > T.[Process Seq]) AS Rank
to
AND T1.[Process Seq] < T.[Process Seq]) AS Rank
Finally, it will be much easier to view the results, at least for
testing purposes, if they are sorted. I recommend adding:
ORDER BY [DOL Process], [Process Seq]
or
ORDER BY [DOL Process], [Process Seq] DESC
in order to see the results more organized.
If performance is a problem, make sure there is an index on [DOL
Process] and [Process Seq] (in that order). Likely, there should
already be such an index to ensure the uniqueness which you ascribed
to this pair of columns. If they are ever not unique, the ranking
will duplicate values where they are tied.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
Tom, thanks for the response:
The Language is Access 2000 SQL
The Query in question is:
SELECT [20041022_Event Import 22Oct04].Index, [20041022_Event Import
22Oct04].[Revised Event Code for PA SP Liab & Property], [20041022_Event
Import 22Oct04].[DOL Start], [20041022_Event Import 22Oct04].[DOL End],
[20041022_Event Import 22Oct04].[DOL Process], [20041022_Event Import
22Oct04].[ELGAR Event Name (10 NANANM)], [20041022_Event Import
22Oct04].[Group Cat Code PT & Clash], [20041022_Event Import 22Oct04].[CAT
Memo], [20041022_Event Import 22Oct04].[CAT Intervene], [20041022_Event
Import 22Oct04].[CAT Stlmnt], [20041022_Event Import 22Oct04].[Process Seq],
[20041022_Event Import 22Oct04].[Print Memo], [20041022_Event Import
22Oct04].[Category Code], [20041022_Event Import 22Oct04].[Group Cat Code
Flag] INTO EventTest1
FROM [20041022_Event Import 22Oct04];
The ranking field is [Process Seq] which must be unique for each [DOL
Process] where [DOL Process] is a date and [Process Seq] is an integer.
Jeremy King
Tom Ellison said:
Dear Jeremy:
In order to do this, which is usually called ranking, you will need
some field or set of fields that uniquely sort the rows within each
date. The query can be created on that basis, using a correlated
subquery.
If you will provide a query that does everything else you want to see
except this ranking column, I can add that for you. You must specify
wich column is the date column on which it is grouped and you must
specify the column(s) that uniquely order the rows within each date.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
On Fri, 29 Oct 2004 05:30:05 -0700, "Jeremy King" <Jeremy
