ranking data and updating a table

  • Thread starter Thread starter marilyn
  • Start date Start date
M

marilyn

I am not a strong access user, but have trying to learn it by writing a
scoring program for a Scout event at the end of May. I have created a table
for each event (there are 24 events) and have made forms that allow someone
to data enter the data from hard copy scorecard. So the table has the
following fields: number, name, scorecarddata, calculatedscore, place and
class. There is also some calculation performed inserting that data into the
calculatedscore field. Once the events are over I need to insert information
into two column in that table one for place (first place second place etc
which must be able to handle ties) and one for class (top 15% =best, next
30%=excellent, next 35%=good and the rest are needs help). I have tried
many of the things I have found on the posts in this forum, but they either
don’t work, or I get compiling errors. Can anyone help me?
 
You can rank (who, score) like this:

SELECT a.who, 1+COUNT(b.who) AS rank
FROM tableNameHere AS a LEFT JOIN tableNameHere As b
ON a.score > b.score
GROUP BY a.who



For the scale, define it in another table:

Scales ' table name
High, Low, Comment ' fields
1 0.85 best
0.85 0 .55 excellent
0.55 0.20 good
0.20 -1 need help ' records



SELECT a.who,
1+COUNT(b.who) AS rank,
rank /(SELECT COUNT(*) FROM tableNameHere) AS percent
FROM tableNameHere AS a LEFT JOIN tableNameHere As b
ON a.score > b.score
GROUP BY a.who


to compute the rank as a percentage (from 0 to 1), or, if you prefer to make
the lookup directly:


SELECT a.who,
1+COUNT(b.who) AS rank,
rank /(SELECT COUNT(*) FROM tableNameHere) AS percent,
DLookup("comment", "scales", percent & " <= High AND Low> " &
percent )
FROM tableNameHere AS a LEFT JOIN tableNameHere As b
ON a.score > b.score
GROUP BY a.who



(that won't work if you use something ELSE that a dot as decimal delimiter,
in your regional setting).



Hoping it may help,
Vanderghast, Access MVP
 
Thank you Michel for taking the time to help me. As I said, I am not a
power access user and am struggling with your use of a.who and b.who. Do I
need to compare two different tables?
thanks
Marilyn
 
A and B are separate references to a "copy" of the same table.

A.Who would be a field named who in your table.
B.Who would be a field named who in that same table.

You can "alias" a table to any name, just as you can alias a field to a
different name. I think you can replace who with name in the query.
HOWEVER, since name is a reserved word, you should use [Name] to prevent
confusion on the part of Access as to whether you mean the Name
property of an object or the field named "Name".



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Thank you John and Michel. I now have the select queries working. Is there
a way to have this data populate a column in the database. Here is the
select query that works:

SELECT a.regattanumber, 1+count(b.regattanumber) AS place
FROM tablet1 AS a LEFT JOIN tablet1 AS b ON a.calculatedscore
b.calculatedscore
Click to expand...
GROUP BY a.regattanumber;

thanks


John Spencer said:
A and B are separate references to a "copy" of the same table.

A.Who would be a field named who in your table.
B.Who would be a field named who in that same table.

You can "alias" a table to any name, just as you can alias a field to a
different name. I think you can replace who with name in the query.
HOWEVER, since name is a reserved word, you should use [Name] to prevent
confusion on the part of Access as to whether you mean the Name
property of an object or the field named "Name".



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thank you Michel for taking the time to help me. As I said, I am not a
power access user and am struggling with your use of a.who and b.who. Do I
need to compare two different tables?
thanks
Marilyn
Click to expand...
Click to expand...
 
Back
Top