Number all the rows by criteria

[Artemiisa]

Hello everyone

This is my first post on this forum, and I'll start by thanking your help!

My question is: I'm trying to create something that will allow me to count the number of records that belong to the same category and then number those rows by order of date.

I've searched the forums but honestly, I may not be using the terms that I should while searching :/

Please see below the practical example of what I'm trying to achieve.

Category	Date	Rank
A	02/08/2020	3
A	01/04/2020	1
A	06/05/2020	2
B	05/09/2020	2
B	24/08/2020	1
C	27/09/2020	1

So:
By each Category (A/B/C, for example) I want to be able to count the records that belong to it, and then I want to number them (in the Rank column), by the Date column in ascending order.

This is supposed to be updatable, so even if an older date is input in the table, it should always replace the rank with the correct order.

If I'm not being clear enough, I'm sorry and I'll try to explain better.

I don't have any code to show, because I don't know where to start to build it :/

Thank you very much for your help! <3

[isladogs]

You can create an aggregate (totals) query to count the records as required. In query design view, click Totals on the Design ribbon.
Add the fields required, change Group By to Count for the appropriate field.

Not sure how your Rank column fits your description but you can sort the date field used in ascending or descending order

[ranman256]

i ripped this code from my other sub, but it works by you
creating a query to sort the data in the order to be ranked. Cata, DAte.
here its called "qsSortData"

then assign these variables in the code.
pvQry = "qsSortData"
pvFld2Check = "Catagory"
pvChgFld = "Rank"

then run: RankAllRecs

Public Sub RankAllRecs()
'pvQry = query name
'pvChgFld    = field to change when duplicate is found
Dim vMsg
Dim db As Database
Dim rst   'As Recordset
Dim qdf As QueryDef
Dim vCurrDup, vPrevDup, vKey, vCurrFld, vAddr, vPrevFld
Dim pvQry, pvFld2Check, pvChgFld
Dim i As Integer
On Error GoTo ErrRemove
'DoCmd.Hourglass True
  'set params
pvQry = "qsSortData"
pvFld2Check = "Catagory"
pvChgFld = "Rank"

Set db = currentdb
Set qdf = db.QueryDefs(pvQry)
Set rst = qdf.openRecordset(dbOpenDynaset)
vPrevFld = "*&%"
With rst
   While Not .EOF
        vCurrFld = .Fields(pvFld2Check).Value & ""
           
           If vPrevFld = "*&%" Then vPrevFld = vCurrFld
                '-----------------------
                'MARK the like values
                '-----------------------
              If vPrevFld = vCurrFld Then          'mark this
                 i = i + 1
                 GoSub UpdFld
              Else
                 i = 1
                 GoSub UpdFld
              End If
           vPrevFld = vCurrFld
       
       .MoveNext
  Wend
UpdFld:
    .Edit
       .Fields(pvChgFld) = i
    .Update
Return
End With
endit:
Set qdf = Nothing
Set rst = Nothing
Set db = Nothing
DoCmd.OpenQuery pvQry
'MsgBox "Done", , "Remove Dupes"
Exit Sub
ErrRemove:
If Err = 3021 Then GoTo endit
MsgBox Err.Description, , "rank():" & Err
Resume endit
Resume
End Sub

[Artemiisa]

Thank you both for your fast responses!
Tomorrow as soon as I get to work I'll try your answers!