query to update a counter field

[mfmuc]

Hi,
I need a MS Access query to update a counter. The counter should start with value 1.
When 2 fields are changing then the counter should start again with 1.
Example:
Customer Invoicing_mounth: Counter
4711 10/2017 1
4711 10/2017 2
4711 10/2017 3
4755 10/2017 1
4755 10/2017 2
4755 11/2017 1




Can someone please help me. Thank you - Martin

[Gina Maylone]

I use something like this to set the sequence of an Estimate Number.

Me.sequence = Nz(DMax("[Sequence]", "job", "Year([received Date]) = " & Year(Date)), 0) + 1
Me.JOBID = "E" & Format(Now(), "yy") & "-" & Format([sequence], "000")

For your application, your invoice number would be equal to my "year" I believe.

HTH.

[ArviLaanemets]

Why bother with counter - simply calculate the number of transactions for same Client? and month. It will be same for all group, but anyway you never can say that 4711 10/2017 1 was earlier than 4711 10/2017 3 (unless you calculate this counter based on some other info in original tables like transaction date or autonumeric ID), because position of row in table has no real meaning in relational databases (the only exception I'm aware of is FoxPro).