ACCESS FEED: Re: [MS_AccessPros] RANKING

Jumat, 15 April 2016

Re: [MS_AccessPros] RANKING

Thanks very much. I did not know that the "AS" keyword can be omitted between a table name and its desired alias. Why is your DCount method much less efficient?

Dave W

----- Original Message -----

From: Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]

To: Access Professionals Yahoo Group

Sent: Friday, April 15, 2016 12:01 AM

Subject: RE: [MS_AccessPros] RANKING

I used a subquery to return the rank. The subquery uses a copy of the same table and simply counts the number of records in the copy where the score <= the score in the original table.

You could also use DCount() but that would be much less efficient:

SELECT tblAde.StudentName, tblAde.Score, DCount("*", "tblAde", "Score <=" & Score ) AS Rank

FROM tblAde;

Duane

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Thu, 14 Apr 2016 23:12:03 +0100
Subject: Re: [MS_AccessPros] RANKING

Duane,

I am very interested in your solution as it may be helpful to me also, but I don't understand it.

How does the "FROM tblAde A WHERE A.Score <= tblAde.Score" part work?

Dave W

----- Original Message -----

From: Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]

To: Access Professionals Yahoo Group

Sent: Wednesday, April 13, 2016 6:45 PM

Subject: RE: [MS_AccessPros] RANKING

Ade,

The typical method using SQL like:

SELECT tblAde.StudentName, tblAde.Score, (SELECT COUNT(*) FROM tblAde A WHERE A.Score <=tblAde.Score ) AS Rank

FROM tblAde;

This doesn't account for your using ".5" for ties. Before working on any type of solution for this, we would need to know what this would like like if there is a 3-way tie or even more.

Duane Hookom, MVP
MS Access

To: MS_Access_Professionals@yahoogroups.com