Calculating relative record numbers

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
0
Utf
11/26/2007 9:01:01 PM
access.queries 6343 articles. 1 followers. Follow

16 Replies
1419 Views

Similar Articles

[PageSpeed] 1

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.

-- 
Marsh
MVP [MS Access]
0
Marshall
11/26/2007 10:50:09 PM
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];

-- 
KARL DEWEY
Build a little - Test a little


"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
> 
> any and all help is appreciated
0
Utf
11/26/2007 11:00:01 PM
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.
> 
> -- 
> Marsh
> MVP [MS Access]
> 
0
Utf
11/26/2007 11:15:03 PM
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.
0
Marshall
11/27/2007 12:54:07 AM
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

"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.
> 
0
Utf
11/27/2007 1:43:00 AM
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.

"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.
> 
0
Utf
11/27/2007 2:23:00 AM
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.
> 
0
Utf
11/27/2007 2:36:00 AM
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]


Steve S wrote:
>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. 
>> 
>> 
>> 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.
>> 

0
Marshall
11/27/2007 4:18:44 AM
Steve S wrote:

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

-- 
Marsh
MVP [MS Access]
0
Marshall
11/27/2007 4:22:59 AM
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@discussions.microsoft.com> wrote in message 
news:14754875-ED54-4845-9B70-24C702BC1DB1@microsoft.com...
> 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.
>> 


0
Michel
11/27/2007 3:14:37 PM
Michel Walsh wrote:

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

-- 
Marsh
MVP [MS Access]
0
Marshall
11/27/2007 6:23:38 PM
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

"Marshall Barton" <marshbarton@wowway.com> wrote in message 
news:snlok3hpgc0gsmc59lk4kptce1l9ou5j3e@4ax.com...
> Michel Walsh wrote:
>
>>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.
>
> -- 
> Marsh
> MVP [MS Access] 


0
Michel
11/27/2007 6:55:28 PM
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 


0
Michel
11/27/2007 7:14:37 PM
Michel Walsh wrote:

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

-- 
Marsh
MVP [MS Access]
0
Marshall
11/27/2007 8:02:31 PM
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


"Marshall Barton" <marshbarton@wowway.com> wrote in message 
news:rnsok35nkkjdell5u8kv3jqpos8qarc6d0@4ax.com...
> Michel Walsh wrote:
>
>>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.
>
> -- 
> Marsh
> MVP [MS Access] 


0
Michel
11/27/2007 8:14:20 PM
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" <marshbarton@wowway.com> wrote in message 
news:rnsok35nkkjdell5u8kv3jqpos8qarc6d0@4ax.com...
> Michel Walsh wrote:
>
>>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.
>
> -- 
> Marsh
> MVP [MS Access] 


0
Michel
11/28/2007 11:39:46 AM
Reply:

Similar Artilces:

call a phone number from Access by clicking
Hi, Is there any code or macro to input in a control to be able to call a phone number with my telephone tool by clicking the phone control in Access ? Thanks for any help LinaBrussels "LinaBrussels" <LinaBrussels@discussions.microsoft.com> wrote in message news:687004A6-8807-4B85-AF52-B4F3E1928169@microsoft.com... > Hi, > Is there any code or macro to input in a control to be able to call a phone > number with my telephone tool by clicking the phone control in Access ? > Thanks for any help > LinaBrussels If I remember correctly, one of the options, when usi...

CSocket and related classes
Hey, i just recently read an article stating that CSocket and it's related classes are buggy and badly designed... now i could spend ages investigating that but that's wasted time soooo could any of you guys/gals with experience with these classes let me know if there are any nasty suprises lurking in these classes? thanks for your time "Cronus" <dark_f8@hotmail.com> wrote in message news:XR8Kg.5484$DB3.3397@newsfe6-gui.ntli.net... > Hey, i just recently read an article stating that CSocket and it's related > classes are buggy and badly designed... no...

Word 2003 Paragraph Numbering when combining multiple documents
I have a multi-chapter document when has paragraphs numbered 1., 1.1., 1.1.1., 2., 2.1., etc. where the first digit signifies the chapter. I am using heading styles to organize the numbering in the document and it works well - when all of the chapters are in a single document. To facilitate editing and review, I have broken each chapter into a separate document with the idea that I would either combine them using "Insert File" or "IncludeText" fields. I am also using RD fields to create a master TOC and Index for the "combined" document. I have di...

Challenging problem! Recording the time in an adjacent cell when text is entered
Does anyone have a way of using excel as follows: if I enter text in say cell A1 how can I ask excel to record the exact time I entered that text (in minutes and seconds) in say cell B1 I assume you can use the clock on your computer or maybe a stopwatch VBA plug in that counts time up or down in excel?? The reason for this is I'm wanting to use excel, run on my laptop, to record when various events happen on the basketball court such as an intercept, basket in the circle, basket outside the circle etc. Cheers, Snowy Australia. ------------------------------------------------ ~~ M...

Records not in order as input
I have a form that users enter info into. In this particular case, the user was entering info in what appeared to be record 201 according to the record selector at the bottom of the page. When they went to look at this record later it was not there. The user had to use the record selector at the bottom to find it and it was actualy record number 165. Why does this happen? Thanks, CEV Access does not store records in any particular order. Tables are basically just a container of records and the order of the records would need to be determined by using a query rather than the table d...

Business Card - Merge
....I want it to come out like the print preview.... I am creating business cards via a merge in v2003. I have an excel record source with 7 rows in it. When I print, I want 1 page to print, 10 up, with all 10 coming from the same record in Excel. Then page 2 to have all 10 cards with data from record 2, etc. Now I am getting just 1 page with 7 cards and each card is from a different record in Excel. Thanks for any info... If you have setup the Excel with 7 fields and nothing else, open the merge wizard in Publisher setup your business card (you should have only one card on your scree...

Small numbers in the top corner "Xs"
Hello, I have a small number "2" in the minimize box and a number in all of the boxes at the top of my page, where you minimize and maximize the page; also if I go to a page that has a box to check, you can't see the box clearly to check it off...does anyone know how I can fix this problem please?! Thanks! Just a guess... Does it happen in all Windows programs? If yes, then it sounds like the font that is used for those symbols got mixed up. Try Windows start button|Run|charmap (and hit ok). Select the Marlett font. In win98, about half way over of the first line, I s...

CRM3.0
Hi all, Is it possible to use ISV.CONFIG in order to customize "email" entity (for example : adding a button) ? Same question with "Relation" entity. Thanx Manu ...

Excell: Add superscripted footnote beside a number i.e. 10,000 1
Is there a way to add a superscripted number beside a number or formula in cell that refers to a note at the bottom of the page? There is a way to format it, but I am not aware that you can make it work dinamically as in Word, for example. To format it, edit the formula, select the part you want to superscript in the formula bar, go to Format->Cells, and activate superscript in the effects section. Hope this helps, Miguel. "Craig Jr" wrote: > Is there a way to add a superscripted number beside a number or formula in > cell that refers to a note at the bottom of the ...

how excel calculates the major units on an axis when MajorUnitIsAuto property set to true
I need to know, how excel calculates the major units on an axis when MajorUnitIsAuto property set to true. We are porting an existing excel chart application to .net with help of a third party tool for charting. If MajorUnitIsAuto property set to true, excel automatically calculates the major unit.Need to know what algorithm excel uses to calculate the Major Units. Thanks in advance, Mathew ...

Percentage tax calculation
Hi, I need to apply tax rate, in percentage form, not in absolute form. The final goal is to apply a different % tax rate, based on the country of my customer. But apply manually a percentage may be sufficient... Does anyone have a suggestion abt how i can do that ? Thanks, Joe ...

Last record
When I use ctrl/end in my worksheet, I end up in column HH:93 even though as far as I can see I only have data through I:93. How can I find the ghost data Excel thinks is there? Hi CS, See Debra Dalgleish's notes: http://www.contextures.com/xlfaqApp.html#Unused --- Regards, Norman "CS" <anonymous@discussions.microsoft.com> wrote in message news:475201c47357$78685930$a301280a@phx.gbl... > When I use ctrl/end in my worksheet, I end up in column > HH:93 even though as far as I can see I only have data > through I:93. How can I find the ghost data Ex...

Import txt file with multiple rows for each record
I have a royalty report from SAP that I need to bring into Excel. It includes check number, payment date, amount, and check recipient. The problem is the "check recipient" information comes in on either 1,2,3, or 4 lines in the report. But the rest of the record information is all on line 1. This is random and will not be the EXACT same structure each month. Ex. 2539893 3/14/2002 55.09 Jane Doe Anytown USA 2539894 3/14/2002 79.99 Bob Evans 2539895 3/14/2002 5.00 John Smith 111 Mai...

Numbering a Cell
Hi, I have created a Invoice in Excel from my Office 2000, and saved it as an Excel template, I have a invoice number in Cell E14 which is 16 at this time, I want this number to be automaticaly increased by 1 each time I open the Template. Is this possible? If it is some very basic simple to follow instructions on how to do it would be most helpful> Many thanks Andy JL Try this in the workbook object Private Sub Workbook_Open() Sheet1.Range("E14").Value = Sheet1.Range("E14").Value + 1 End Sub Sandy "Andy JL" <AndyJL@discussions.microsoft.com>...

Create a new Parts record from an exiting one
I would like to create a form that prompts for an EXISTING part number record in my tblParts and then creates a NEW part record for a prompted NEW part number. All of the existing field values would be "copied" to the new part record. I have looked around for a solution to this but have had no luck. Can anyone point me to some examples of this? -- Peter Marshall Manager Information Services Ohio Coatings Company (740) 859-5560 office (304) 281-7404 cell Peter, how about adding an unbound combo to the top of your form, where the user selects the part they want to copy. Th...

Tag records where a certain field contains all Uppercase Letters.
Is there a way to tag records where a certain field has letters that are all Capitalized? It's a long story but this is a very raw dataset and all the values in a column that are all capitalized represent the Company Name and I need to isolate all these records and pull them out of the dataset. Thanks, Ken =?Utf-8?B?S1BS?= <KPR@discussions.microsoft.com> wrote in news:687D324E-3F7A-47CF-900F-4869F2E83F41@microsoft.com: > Is there a way to tag records where a certain field has letters > that are all Capitalized? It's a long story but this is a very raw > dataset an...

ComboBox: Update a value in current record before changing the ComboBox Value
It's been a while since I have had any issues...I seem to create my own problems... I have a combo box in a sunform. I use the combobox to choice multiple items in the subform. When I pick and item in the combobox, I run an AFTER UPDATE and set a value in a table (I change a 0 to a 5). However, if I picked the wrong record and go and change it to a different record, the value I set to 5 above stays a 5 and know I have a record with a value I do not want...I want to change it back to 0 before I change the record in the combobox. I have tried Before Update, On Enter, On Get Focus...thes...

Number Format Issue
It seems that from time to time, some of our numbers get into Excel 2002 as text and will not sort properly. I know how to fix, by using the Multiply feature in Paste Special to multiply all the offending numbers by 1. The question is, what causes this to occur when we start out with the General format, and certainly do not format the cells as text? Thanks in advance Hi, Do you paste anything into the sheet? Do you copy from one sheet to another? Do you imprt or get external data into this sheet? >-----Original Message----- >It seems that from time to time, some of our numbers ...

Numbering items from a list in order
Does someone have a method for identifying a set of numbers by their position within in a list. For example 0,0,0,10,0,0,5,0,2 If I want to identify all the items that are not equal to zero, by their position 10 would return 4, 5 would return 7, and 2 would return 9 I need to identify every occurance. -- Natalie =MATCH(10,A1:A20,0) -- HTH RP (remove nothere from the email address if mailing direct) "Natalie" <Natalie@discussions.microsoft.com> wrote in message news:2E92C887-0574-4E20-B2F6-B0DA7AE66C93@microsoft.com... > Does someone have a method for identifying a ...

Formatting footnotes when paragraph numbering is turned on
Apologies if this turns out to be a duplicate posting ... my previous post appears to have failed. When I have paragraph numbering turned on, Word insists on assigning a paragraph number to my footnotes as well as a footnote number. I can manually delete the paragraph number, but would prefer to turn off the option. More important, however, is that Word also assigns a paragraph number to the line drawn across the page above the footnotes, and I cannot find a way to remove this. Can somebody offer me a suggestion on what to do? Please see reply to your previous duplicate (n...

Mask Social Security Numbers
I would like to mask the first 5 numbers of the social security number that prints on the payroll stub. I realize that I can make them invisible i.e. not print them however the objective is to mask the first 5 Numbers. Anyone have a suggestion. Thank you. -- JIM Jim in a calc. field , use a formula to get the right 4 digits of the ssn then, in a new Calc.field concatenate xxxxx with the formula above You can now put this on the report. HS "Jim" <Jim@discussions.microsoft.com> wrote in message news:B4C56DE7-BD6D-408C-9BB7-714A4323D211@microsoft.com... :I would like to ...

How do I set number of pages in every group of folding in booklet? #2
This is the first time I use Microsoft Publisher to publish my book. I made a booklet which consist of around 48 pages. However, it printed the whole pages pear and pear. Page 1 with page 48, page 2 with page 47, page 3 with page 46 and so on. I want to arrange the number of pages in group of folding. For instance in every 16 pages, so that page 1 with page 16, page 2 with 15, page 3 with 14 and so on in one group, then page 17 with page 32, page 18 with 31, .... I want the whole 48 pages in one publishing document, so that I get 3 group of folding. Each consist of 8 sheets of paper. H...

Auto numbering in MSCRM
How does MSCRM handle auto numbering of Quotes, Orders and Invoice etc. Since there settings are global seetings and not for each individual users. The question we have is, where remote user while being "offline" create either an Order or an Invoice at the same time back the the office "online" user also create an Order or an Invoice and system generated the same Order or Invoice number for both users. When "offline" user come back to the office and sync his/her laptop, what will happen to the order or invoice created which supposely has the same number as...

Number of days falling in various years
I am starting two dates in my example. The first is 6/20/02 and the second is 1/15/2006. I need to calculate in separate columns for years 2001 through 2006 the number of day in this year that are between these two dates. For example, in 2001, the answer is -, in 2002, the answer is 194, and in years 2003 through 2005, the answer is 365, and finally in 2006, the answer is 15 days. The formula below almost works, but 2001 it came up with a negative number. =MIN(IF(DATE(O1,12,31)<$K$2,0,MIN((DATE(O1,12,31)-$K$2), 365)),IF(DATE(O1,12,31)<$J$2,MIN((DATE(O1,12,31)-$K$2),365),G2...

formula to calculate the week number from a date?
is there a formula or nice way without brute force? TIA luis See http://www.cpearson.com/excel/weeknum.htm In article <eqY6O7EkEHA.3876@TK2MSFTNGP15.phx.gbl>, "luis" <doesntexist@anyserver.com> wrote: > is there a formula or nice way without brute force? Hi How is a week defined by you. It's different for different countries. P.e. here in Estonia week starts with Monday. And first week of year is the one, where first Thuesday of year is falling in. Arvi Laanemets "luis" <doesntexist@anyserver.com> wrote in message news:eqY6O7EkEHA.3...