auto numbering records

[Debbie]

I have two tables, the 1st contains employee information,
the 2nd contains information on discipline cases related
to the employee. I need to create a field which will
number the individual discipline cases for each employee.
For example, if an employee has six cases, they will be
numbered 1-6, another employee with 3 cases will be
numbered 1-3, etc. Any assistance appreciated.

 

[Steve Schapel]

Debbie,

First let me issue the standard obligatory disclaimer: This is
derived data and you should not have a field for it. You can obtain
such an indicator using a ranking function, whenever you need it for
your purposes of form or report.

If you have an exceptional circumstance which required this number in
a table field, you will need to use a macro or VBA procedure on the
AfterUpdate event property of the EmployeeID control when you are
entering a new discipline case. Easiest is to set its value (with
SetValue if you are doing it with a macro) to the equivalent of...
Nz(DMax("[CaseRank]","Cases","[EmployeeID]=" & [EmployeeID]),0)+1

- Steve Schapel, Microsoft Access MVP