Calculating relative record numbers

[Steve S]

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

any and all help is appreciated

 

[Marshall Barton]

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.

 

[KARL DEWEY]

Try this --
SELECT Q.[Judge ID], Q.[Contest ID], (SELECT COUNT(*) FROM Judges Q1
WHERE Q1.[Judge ID]& Q1.[Contest ID] <= Q.[Judge ID]&Q.[Contest ID]
AND Q.[Contest ID] = [Enter Contest ID] AND Q1.[Contest ID] = [Enter Contest
ID] ) AS Rank
FROM Judges AS Q
WHERE (((Q.[Contest ID])=[Enter Contest ID]))
ORDER BY Q.[Judge ID], Q.[Contest ID];

 

[Steve S]

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).

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.

 

[Marshall Barton]

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]

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).

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.

 

[Steve S]

Thanks for your help Marshall.

I wish it was that easy. I need this query to feed another query that
creates records for a report. I need the relative number to build records
for several subreports. Relativejudge1's scores will be displayed in column
1 and so forth via a loop in a VBA module. I tried using a crosstab query
but find that I have to creae my own by burte force. If I can get this to
work I will post the VBA code and that will make more sence (I hope).

thanks again

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]

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).

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.

 

[Steve S]

Woops. I forgot to mention that there may be (and in most cases are)
duplicate [judge ID]'s . what I get ia great until a duplicate is found.
then I get 1,2,3,5,5,6,7. what I need is 1,2,3,4,4,5,6. How to fix???

thanks much.

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]

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).

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.

 

[Steve S]

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?

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]

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).

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.

 

[Marshall Barton]

I have had trouble using a subquery inside a subquery so I
think you need to create another query (named DJudges) just
for the counting:

SELECT DISTINCT [Judge ID], [Contest ID] FROM Judges

Then, I think this will do what you want:

SELECT Judges.[Judge ID], Judges.[Contest ID],
(SELECT Count(*)
FROM DJudges As X
WHERE X.[Contest ID] = Judges.[Contest ID]
And X.[Judge ID] <= Judges.[Judge ID]) As Rank
FROM Judges
WHERE Judges.[Contest ID] = 30
--
Marsh
MVP [MS Access]

Woops. I forgot to mention that there may be (and in most cases are)
duplicate [judge ID]'s . what I get ia great until a duplicate is found.
then I get 1,2,3,5,5,6,7. what I need is 1,2,3,4,4,5,6.

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).

:

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.

 

[Marshall Barton]

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?

I don't have any idea where the 1 + came from, but the rest
of it is probably on other lines in the field cell. If I'm
right, you can see the entire subquery by right clicking in
the cell and choosing Zoom.

 

[Michel Walsh]

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

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?

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]

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).

:

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.

 

[Marshall Barton]

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]

Michel,

I may not be seeing all the replies in this thread so my
view of the conversation might be distorted.

I think Steve's unusual ranking and the use of criteria
would require that to be:

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

Even if I have that right, I'm not sure it will work because
of the [ ] in the subquery. It would probably better to use
a separate query instead of a subquery?

There is also the trade-off between query performance and
the non-equi join requiring an inexperienced person to work
in SQL view. I am struggling with the quandary of providing
something an OP can understand and providing a good example.

 

[Michel Walsh]

I simplified the query, indeed, as example, I removed any reference to
[judge contest] = 30, since the OP supplied a wanted result where the [judge
contest] values where all equal to 12. Sure, I should have included the
condition on judge contest too, if there are more than a single contest. If
there is more than one judge by contest, a select distinct is required...
but if a judge is listed just once per contest, the query could be
simplified, though, to:


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


where the rank occurs by contest.

Vanderghast, Access MVP

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]

Michel,

I may not be seeing all the replies in this thread so my
view of the conversation might be distorted.

I think Steve's unusual ranking and the use of criteria
would require that to be:

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

Even if I have that right, I'm not sure it will work because
of the [ ] in the subquery. It would probably better to use
a separate query instead of a subquery?

There is also the trade-off between query performance and
the non-equi join requiring an inexperienced person to work
in SQL view. I am struggling with the quandary of providing
something an OP can understand and providing a good example.

 

[Michel Walsh]

Well, it seems the same judge may be listed more than once per contest, so,
the SELECT DISTINCT would be required (or, maybe, using a Crosstab and one
of the trick from Steve Dassin to simulate a DISTINCT COUNT).


Vanderghast, Access MVP

 

[Marshall Barton]

Well, it seems the same judge may be listed more than once per contest, so,
the SELECT DISTINCT would be required (or, maybe, using a Crosstab and one
of the trick from Steve Dassin to simulate a DISTINCT COUNT).

Thanks for the clarifification Michel.

Do you have any comments about using a subquery that
contains square bracketed field names. I think I have seen
that work in newer versions of Access, but then I know it
was not allowed in A97. Something about using ( ) working
until another change was made to the query. I never use
space or other funny characters in field names so I have no
real life experience with the issue.

 

[Michel Walsh]

I don't have either much experience with ill formed name, unfortunately. I
am a so low poor typist, that I avoid all extra complications with extra
[ ] ...


Vanderghast, Access MVP

 

[Michel Walsh]

After further checks, it seems that only the solution with the sub-query is
reliable when there is duplicated values, if you want to get the 'packed
rankings', as it seems to be desired by the OP. A join would require a
DISTINCT COUNT to be reliable (even if we use a virtual table based on
"SELECT DISTINCT [judge id]" as one of the table of the join), but JET does
not have a distinct count built in. A side note, MS SQL Server 2005 has a
DISTINCT COUNT and a RANK construction, so, in case that the OP uses MS SQL
Server 2005, other solutions can be available to him, but with Jet, I have
to remove my propose suggestion to use a join, and stick with Marshall
solution with a sub-query.

Vanderghast, Access MVP

 

[Marshall Barton]

My goodness, I had no idea it was as complex as all that.

I appreciate your efforts to point out the subtle issues
that have to be considered in this kind of query
calculation.
--
Marsh
MVP [MS Access]

After further checks, it seems that only the solution with the sub-query is
reliable when there is duplicated values, if you want to get the 'packed
rankings', as it seems to be desired by the OP. A join would require a
DISTINCT COUNT to be reliable (even if we use a virtual table based on
"SELECT DISTINCT [judge id]" as one of the table of the join), but JET does
not have a distinct count built in. A side note, MS SQL Server 2005 has a
DISTINCT COUNT and a RANK construction, so, in case that the OP uses MS SQL
Server 2005, other solutions can be available to him, but with Jet, I have
to remove my propose suggestion to use a join, and stick with Marshall
solution with a sub-query.

Thanks for the clarifification Michel.

Do you have any comments about using a subquery that
contains square bracketed field names. I think I have seen
that work in newer versions of Access, but then I know it
was not allowed in A97. Something about using ( ) working
until another change was made to the query. I never use
space or other funny characters in field names so I have no
real life experience with the issue.