Ranking by Group

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have read many post on this but I could not get anything to work.

My data looks like this

Intake Date Enrollment Date
1/1/05 1/3/05
1/1/05 2/3/05
1/1/05 3/3/05
2/1/05 1/3/05
2/1/05 2/3/05

and I want to get to this:

Intake Date Enrollment Date Rank
1/1/05 1/3/05 1
1/1/05 2/3/05 2
1/1/05 3/3/05 3
2/1/05 1/3/05 1
2/1/05 2/3/05 2


Many answers I saw on this forum were referring to Q1 and Q and I only have
one query:
SELECT [MBA_Intake].[Intake], [MBA_Intake].[Enddate],
[EnrollmentDates].[Enrollment]
FROM EnrollmentDates, MBA_Intake
WHERE ((([MBA_Intake].[Enddate])<[Enrollment]))
ORDER BY [MBA_Intake].[Intake], [EnrollmentDates].[Enrollment];


Please help!!!

Thank you
 
Try this (change the names to the real names):

SELECT R.[Intake Date], [R.Enrollment Date],
(SELECT Count(*) FROM TableName AS T
WHERE T.[Intake Date]=R.[Intake Date] AND
T.[Enrollment Date] <= R.[Enrollment Date]) AS Ranking
FROM TableName AS R;
 
Back
Top