record counting

[Rosco4]

I have an Access 2000 database table that contains a member# and invoice#. I need to identify the count of invoices by member, and then start the counting over on the change of a member#.

Example:
Member# 1234, Invoice# ABC1, Cnt: 1
Member# 1234, Invoice# XYZ4, Cnt: 2
Member# 1234, Invoice# XDA5, Cnt: 3
Member# 5234, Invoice# ZSF3, Cnt: 1
Member# 5234, Invoice# 5123, Cnt: 2

Thank you!

 

[Nouba]

You could use this query for achieving your goal.

SELECT
  E.Member
, E.Invoice
, (SELECT
    COUNT(*)
  FROM Example
  WHERE
    MEMBER=E.Member AND
    Invoice<E.Invoice)+1 AS Cnt
FROM Example AS E
ORDER BY
  E.Member
, E.Invoice

 

[Rosco4]

Nouba,
The query you suggested worked perfectly. Thank you so much!
You saved me a lot of manual work.