Ranking with Query

[jrsellers05]

I have searched this forum and I still cannot fix my problem.

I have a system to take judge's scores and find the final overall score average. My problem in ranking them is I also have different projects in which to rank.

For example -- all of my scorecards go into one table and a query calculates the score and sorts them, but when i go to rank the final scores, it ranks everyone and not just the people who are in that project.

PLEASE HELP -- NEW TO ACCESS!

Thanks!

J.R.

 

[Pat Hartman]

Change the query so that it only selects the rows you want to consider.

 

[jrsellers05]

But I have over 40 projects and I do not need to create 40 queries!!!!

I need to see if I can do all of the individual rankings in one query!

thanks

 

[mresann]

Why not add a field called "project" to the table you are querying. When you append the data to the table, include the project number. Then, in your query, include the "project" field in the where clause, and you will only return records for that particular project.

 

[jrsellers05]

maybe you dont understand

i MUST have all of the scores in one query, not just one project. If I just did one project / query then i would have 40 queries which is a pain in time and to print 40 reports.

i want to print only one report with one query

i can rank in reports but it does not deal with tied scores - so i can't use that unless i can fix that.

right now i have a report that groups the project scores and then ranks them 1st, 2nd, etc but i need to fix the problem of two people tying within a project.

 

[Dona]

Try this website

http://support.microsoft.com/default.aspx?scid=kb;en-us;208946

Good luck!

Dona

 

[jrsellers05]

THat still does not work

My problem is that I still need either:

A) A report that will group the 40 different project areas together and then rank each individual 1st 2nd 3rd based on their scores. I need the thing to rank it with ties though (not 2nd for 50 and 3rd for 50 like it is now)

OR

B) A query that performs the same task above:

My current Query inlcudes:

Name Project Total Points Total Judges Average Score

The problem with ranking them is that all of the scorecards are in a table and they are grouped in reports by the project. If I put the ranking (like I want) into the query it ranks every scorecard together, regardless of the project they are in. I cannot make it rank just by project w/o creating 40 different queries. I am trying to make it so that this can be used in the future, so more project can be added and no one has to know how to change the query's WHERE statement.

When I get home I can upload my database so that you guys can look at it.

Thanks so much for your help so far!

 

[Pat Hartman]

Use the query suggested by Microsoft as your start. You need to include the project field in the ranking so that you are ranking on TotalPoints and Project. If you don't want duplicate numbers within that, you need to add a third field which will break those ties. I posted an example of this a few days ago. Search for ranking posts by me within the last week.

 

[jrsellers05]

I still don't understand. Maybe you can look at my database and code and let me know what is best!

thanks for sticking with it so far though!

J.R.

 

[Pat Hartman]

I don't really have time to fix this for you. Look at this post. I solved the same problem for this poster a few days ago.
http://www.access-programmers.co.uk/forums/showthread.php?t=79157&highlight=rank

 

[jason_lee]

Hi,

I have two questions about your sample,
can each student have only one project?
what the relation between "Judge Name" and "Project Name", one to one, one to many, many to many or nothing?

regards