Re: Calculating relative record numbers

If you want them in ID order then that will work.

I don't see where you ultimately want to see this numbering,
but if it's in a report, then forget the query approach and
use a running sum text box with the expression =1
--
Marsh
MVP [MS Access]


Steve S wrote:
Couldn't I just use [Judge ID] as the 'sort field?' I do not care what the
relationship is between the [Judge ID] and the relative number - just that
the relative number sequence starts with 1 and and is sequential (linear).

"Marshall Barton" wrote:

Steve S wrote:

The following SQL selects 8 records from a table [Judges].

SELECT Judges.[Judge ID], Judges.[Contest ID]
FROM Judges
WHERE (((Judges.[Contest ID])=30));

I want to add a column that will also display the relative record number as
shown below.

78 12 1
23 12 2
44 12 3
53 12 4
and so on.

Adding the additional column is not the problem: how to calculate or devise
the relative number is the problem


This is usually called a ranking query and it requires one
or more fields that can be used to produce a unique sorting
ot the records.

Here's an example that assumes you have a field named
SortField that meets that requirement:

SELECT Judges.[Judge ID], Judges.[Contest ID],
(SELECT Count(*)
FROM Judges As X
WHERE X.[Contest ID] = Judges.[Contest ID]
And X.SortField <= Judges.SortField) As Rank
FROM Judges
WHERE Judges.[Contest ID] = 30

If that's slow enough to notice, post back and we'll try
another way.
.

Relevant Pages

  • Re: Calculating relative record numbers
    ... If you prefer doing it through a join rather than through a sub query: ... the relative number sequence starts with 1 and and is sequential ... I want to add a column that will also display the relative record ... FROM Judges As X ...
    (microsoft.public.access.queries)
  • Re: Calculating relative record numbers
    ... what I get ia great until a duplicate is found. ... the relative number sequence starts with 1 and and is sequential. ... I want to add a column that will also display the relative record number as ... FROM Judges As X ...
    (microsoft.public.access.queries)
  • Re: Calculating relative record numbers
    ... Steve S wrote: ... I want to add a column that will also display the relative record number as ... FROM Judges As X ...
    (microsoft.public.access.queries)
  • RE: Calculating relative record numbers
    ... FROM Judges AS Q ... KARL DEWEY ... "Steve S" wrote: ... I want to add a column that will also display the relative record number as ...
    (microsoft.public.access.queries)
Loading