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
On Mon, 1 Nov 2004 02:25:02 -0800, "Jeremy King"
<(E-Mail Removed)> wrote:
>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" wrote:
>
>> 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
>> (E-Mail Removed)> wrote:
>>
>> >Using MS Access 2000, I need to create separate numeric sequences within a
>> >table for each change of date to give an unique nummeric counter for each
>> >record within a date. How do I achieve this?
>>
>>
Similar Threads
