countif statement for moving range Access 2013

[Bassem]

Hello,

I'm trying to achieve something similar to a countif formula for a moving range in access 2013.

As the below table shows, for every item that repeats I'd like to have an increment on the count number of that item. The first time item 1 is listed i'd like to have 1, the second time item 1 is listed i'd like to have 2...

Items	Result that Id like to see
Item 1	1
Item 2	1
Item 3	1
Item 1	2
Item 1	3
Item 3	2
Item 2	2

<colgroup><col><col></colgroup><tbody>
</tbody>

Please can anyone help on how this might be achieved within access.

Thanks,

 

[Micron]

If all you wanted was the count of each item, that's just a totals query. The only way I can think of doing this is with code. Assuming there will be more Items than what you've listed, what I'm thinking is not as simple as written here; this is just the idea in a nutshell and would not be not executed exactly in this order:

This uses two recordsets that you create nested loops for.
- create variable for lngItemCounter (long); hereinafter referred to as n
- create recordset rs1 of unique values (likely a query with DISTINCT predicate), ascending order so you know the distinct values (say 13, for example). MoveFirst.
- create recordset rs2 of all value records with two fields; Item and Counter. Do While Not rs.EOF. Set n = 0, FindFirst using rs1 value and set n=n+1. Write n to Counter field for that record (Update the DAO.Recordset). FindNext and increment n and write the new n to Counter until rs1 value is not found. Exit this loop and movenext in rs1 and reset n = 0.
-repeat the rs2 loop with the new rs1 value. When found, n=n+1; write n to Counter. Do this until the end of rs1 is reached.

 

[Bassem]

Thanks for your reply. Let me try and see if I can interpret and use what you have mentioned above as I'm not too familiar with using code for access.

 

[Micron]

I probably don't have the nested loops 100% correct, but here's some air code to get you started.

*lookup 'database.openrecordset method
*I don't know your data type, assuming text

Sub RunningTotal()
Dim db As Dao.Database
Dim rs1 As Dao.Recordset, rs2 As Dao.Recordset
Dim cntr As Long
Dim rs1Val As String

On Error GoTo errHandler
Set db = CurrentDb
Set rs1 = db.OpenRecordset(qryName1)
Set rs2 = db.OpenRecordset("qryName2")

cntr = 0
rs1.MoveFirst
Do While Not rs1.EOF
  rs1Val = rs1.Fields(0) 'assuming the itemValue is the FIRST field of the recordset
  rs2.FindFirst [rs1Val]
    Do While Not rs2.NoMatch
      With rs2
        .Edit
        !Fields(1) = cntr + 1 'this is the SECOND field of the recordset
        .Update
      End With
      rs2.FindNext [rs1Val]
    Loop
  rs1.MoveNext 'rs1Val should change after this line is executed
  cntr = 0 'reset counter for the next item value in rs1
Loop

exitHere:
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing

Exit Sub

errHandler:
'do whatever to handle errors such as:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere

End Sub

 

[Bassem]

Micron - Thanks for your help but I found another solution. A fellow employee had a look at my problem and gave me the below formula to put in builder.

DCount("[ID]","Qry_Item #s & Packing Lists","[ID]<" & [ID] & " and [Item Number] ='" & [Item Number] & "'")+1

He actually added the ID field from a previous table and then used it to come up with something that resembles a running total.

Sorry to have bothered you with writing a code for me and thanks again for your help.

Regards,

 

[Micron]

No problem. Keeps my old brain from getting stale.