Re: Calculating relative record numbers

If you prefer doing it through a join rather than through a sub query:


SELECT a.[judge id], a.[judge contest], 1+COUNT(b.[judge id])
FROM myTable AS a LEFT JOIN myTable AS b
ON a.[judge id] > b.[judge id]
GROUP BY a.[judge id], a.[judge contest]



Hoping it may help,
Vanderghast, Access MVP



"Steve S" <SteveS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:14754875-ED54-4845-9B70-24C702BC1DB1@xxxxxxxxxxxxxxxx
Also noticed that when the AQL is configured back into the QBE grid only
Rank: 1+(SELECT Count(*) shows. the rest of the syntax is not shown. Why
is this? I know that SQL strings like unions will not revert back into
the
grid but this is the first time I have see only partial syntax. Is there
ever an end to the learning curve?

"Marshall Barton" wrote:

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
    ... I need this query to feed another query that ... creates records for a report. ... the relative number sequence starts with 1 and and is sequential. ... FROM Judges As X ...
    (microsoft.public.access.queries)
  • Re: Calculating relative record numbers
    ... I have had trouble using a subquery inside a subquery so I ... think you need to create another query (named DJudges) just ... FROM Judges ... This is usually called a ranking query and it requires one ...
    (microsoft.public.access.queries)
  • Re: Getting Rows into Columns
    ... to find a way to have a query get me the following information. ... If there is 2 judges then judge3 would be blank or zero. ... qryRank As q ...
    (microsoft.public.access.queries)
  • Re: Spin calling John 100% identity (Human & Gekko)
    ... Unfortunately there are no other sequences to use in checking the accuracy of that EST, i.e. no Gekko atpase sequences, and the study the sequence comes from is unpublished. ... Query 4427 ... Sbjct 6 ... TCTGACATGGGGCCACCCCACAGGTCAGAGTGGTGGTAGAACCCCTTCAGGACTCCCAGC 245 ...
    (talk.origins)
  • Re: Group By Sequential Records
    ... Is there any way to speed up the query? ... in each "sequence" where Group and AltID are the same and ID numbers are ... remains the same throughout each "sequence". ... FROM AddrCent AS Sub ...
    (microsoft.public.access.queries)