How to normalize monthly data in an ordered numerical sequence???

[carojasa]

June 7
I attempted to follow the example of the link provided. I failed to maintain the ranking to within the ID numbers. And it seems like access ranked the entire table. Any suggestions?

[orange]

So this is what you're looking for re numbering (rank is rightmost column)

1171 Jun-14 120 1
1171 Jul-14 346 2
1171 Aug-14 743 3
1171 Sep-14 708 4
1171 Oct-14 744 5
3045 Oct-12 169 1
3045 Nov-12 536 2
3045 Dec-12 729 3
3045 Jan-13 744 4
3045 Feb-13 672 5
3045 Mar-13 743 6
3045 Apr-13 706 7
3045 May-13 744 8
3045 Jun-13 720 9
3045 Jul-13 744 10
3045 Aug-13 744 11
3045 Sep-13 720 12
3045 Oct-13 744 13
3045 Nov-13 577 14
3045 Dec-13 720 15
3045 Jan-14 744 16
3045 Feb-14 646 17
3045 Mar-14 743 18
3045 Apr-14 720 19
3045 May-14 720 20
3045 Jun-14 720 21
3045 Jul-14 721 22
3045 Aug-14 690 23
3045 Sep-14 552 24
3045 Oct-14 744 25
4188 Nov-12 480 1

[June7]

Post SQL statement of attempted query.

http://stackoverflow.com/questions/4...rouped-ranking

[carojasa]

Orange.
That is basically what i need.
Oh lord. Can you post the procedure please? I will be forever grateful

[June7]

Try:

SELECT *, (SELECT Count(ID) AS HowMany FROM tablename AS Dupe WHERE Dupe.ID=tablename.ID AND Dupe.[Date]>tablename.[Date] ORDER BY Dupe.ID, Dupe.[Date]) AS GrpSeq FROM tablename;

[orange]

I have tried a few queries based on my searching and June's links. Your date field is not recognized as a Date and using it in a ranking query is not working.
I have 2 solutions to your issue.

1. Using a small procedure. I took your sample txt file. I changed the name date to mDate since date is a reserved word. I imported it into a table called SAMPLE and let access add a primary Key autonumber as field ID1.
Since the file was ordered on ID and your date field, I just read the file and displayed the ID,Date and a sequence number I created. When the ID changed I reset the sequencer to 1. I showed you the output display which was what you needed.
I then added a Ranking field to the table structure, adjusted my code to update the Ranking field with the sequence I calculated.

I'll attach the procedure to this post. The output of the procedure was shown in post #17.

2. Realizing your date value was not recognized as a date, I tested an expression to get a proper date. Then added a field to your table called RealDate. The SQL to change date( actually field my mdate) to a valid date is

UPDATE Sample SET Sample.RealDate = CDate(Left([mdate],3) & "/20" & Right([mdate],2));

Note: I am using a Canadian Date format.

Now I can use RealDate for proper comparison.

After some testing, I created this query to produce a QryRank -- a ranking sequence by ID and RealDate

SELECT *
FROM 
(SELECT a1.ID, a1.realdate, a1.[PRD Monthly HRS hrs], COUNT(*) AS QryRank  FROM Sample AS a1 
 INNER JOIN 
 Sample AS a2 ON
 (a1.realdate >= a2.realdate) AND
 (a1.id = a2.id) 
GROUP BY 
a1.ID
, a1.realdate
, a1.[PRD Monthly HRS hrs])  AS RankingQuery;

Note: The rankings produced by each method were the same.

Following is the procedure to do the Ranking:

'---------------------------------------------------------------------------------------
' Procedure : RankEm
' Author    : mellon
' Date      : 03/02/2015
' Purpose   : Add a ranking value by group ID top a set of ordered records.
'
' Records were ordered on receipt and an autonumber PK added
' The date field within the OP's record is MMM-99 and is not recognized as a Date
' So I imported his data from txt tab delimited and add the PK auto to keep sequence.
'---------------------------------------------------------------------------------------
'
Sub RankEm()

          Dim db As DAO.Database
          Dim rs As DAO.Recordset
          Dim i As Integer            ' counter to do sequence
          Dim sql As String
          Dim holdID As Long          'temporary variable to hold the current ID and check for changes
10       On Error GoTo RankEm_Error

20        holdID = 27                 'arbitrary starting value
30        Set rs = CurrentDb.OpenRecordset("select sample.*  from sample order by Id1")
40        Do While Not rs.EOF
50            If rs!id <> holdID Then  'is id of this record NOT the same as HoldID
60                holdID = rs!id       'update holdid with current ID
70                i = 1                 ' set i =1
80            Else
90                i = i + 1             'id = holdid so increment i
100           End If
110           Debug.Print rs!id & " " & rs!MDate & " " & rs![PRD Monthly HRS hrs] & "  " & i
120           rs.Edit
130           rs!ranking = i            'update the Ranking value to current i
140           rs.Update
150           rs.MoveNext
160       Loop

170      On Error GoTo 0
180      Exit Sub

RankEm_Error:

190       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure RankEm of Module AWF_Related"

End Sub

Here is a sample of the rankings based on the query (method 2)

ID
realdate
PRD Monthly HRS hrs
QryRank

216
01/03/2014
0
1

216
01/04/2014
0
2

216
01/05/2014
0
3

216
01/06/2014
0
4

216
01/07/2014
0
5

216
01/08/2014
0
6

216
01/09/2014
0
7

216
01/10/2014
0
8

717
01/10/2012
190
1

717
01/11/2012
700
2

717
01/12/2012
676
3

717
01/01/2013
736
4

717
01/02/2013
664
5

717
01/03/2013
743
6

717
01/04/2013
720
7

717
01/05/2013
744
8

717
01/06/2013
714
9

717
01/07/2013
744
10

717
01/08/2013
744
11

717
01/09/2013
720
12

I hope this is useful.

Good luck with your project.

[carojasa]

I will try the code in the morning. I really appreciate your help. I will post as soon as I get the code running. Thank you kindly

[carojasa]

Orange and June7!!!
You have solved my problem!!
The procedure outlined above worked well. I have saved the procedure as a layer in my Queries List! Thank you so much!!!