473,388 Members | 1,309 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,388 software developers and data experts.

Ranking Problem in Access

I have tried to rank records in Access Table with some criteria.
In a School DB the will be on the basis of Marks, but the Criteria is the student should be ranked only their own CLASS and SEC.
I have tried this like....
SELECT School.ID, School.NAME, School.SCHOOL, School.CLASS, School.SEC, School.MARKS, (SELECT Count(*) FROM School_Marks AS [XX] WHERE [XX].SCHOOL = School_Marks.SCHOOL AND [XX].CLASS = School_Marks.CLASS AND [XX].SEC = School_Marks.SEC AND [XX].MARKS > School_Marks.MARKS) AS Rank

Now there is some problem Like......

Happening
Marks Rank
100 1
99 2
99 2
98 4
98 4
97 6
97 6
97 6
96 9

I Want
Marks Rank
100 1
99 2
99 2
98 3
98 3
97 4
97 4
97 4
96 5

If is there any solution please help me......
Thanking you

Regards
Indra Nath Lahiry
Mar 21 '12 #1
1 1218
Rabbit
12,516 Expert Mod 8TB
Do the ranking on a distinct version and then join that back to the main table.
Mar 21 '12 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

11
by: Petre Huile | last post by:
I have designed a site for a client, but they have hired an internet marketing person to incrase their search engine ranking and traffic. He wants to put extra-large fonts on every page which will...
12
by: Irene | last post by:
Hi all again, Well, I have my Athletics database with Athletes, Competitions, Scores tables. I have a ranking query where I get back the list of the competitions-athletes and scores...
5
by: ED | last post by:
I currently have vba code that ranks employees based on their average job time ordered by their region, zone, and job code. I currently have vba code that will cycle through a query and ranks each...
1
by: Joseph Bloch | last post by:
In all the threads on ranking in Access queries, I've not seen anything that deals with ranking against a subset of the query results. What I need to do is take the following query results: Dept...
5
by: valglad | last post by:
Hi, The question below was posted about 4 years ago and noone was able to answer it back then. I have virtually the same type of problem so would appreciate if anyone can help. Thanks ...
6
by: sara | last post by:
I hope someone can help with this. Our director wants to have a report that will have the departments (Retail stores) across the top, stores down the side and the RANKING of the YTD dept sales...
8
by: AnndieMac | last post by:
I have an Access 2002 database of inventory count results, and I have been asked to create summaries of the items with the most losses at a store, region and national level. Individually, I have been...
1
by: jjjoic | last post by:
Hi, I use Access 2003 to generate the back-end data for a ColdFusion report at work. The report is sorted by a column and based on the sorting, rankings are assigned to each row(i.e. the biggest...
0
by: AdamT | last post by:
Hi I'm quite new to Access, I can do queries and only a very little bit of code. So please treat me gently. I have a query with the following fields: Area, Engineer and PC. The PC is a percentage...
0
by: sipacate | last post by:
I've got a ranking query that ranks the performance of teams in challenges. The hierarchy of data is as follows: teams have members members have activities activities have activitytypes...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.