Auto set field equal to the number of records

[Accesser]

Hey there,

I have a table/query with a combination primary key and I'm trying to create another field that is automatically numbered with the count or number of records for a particular combination of the PK fields. The fields are:

Doc part of PK
Party part of PK
Number new field to be auto-numbered

For instance, the following combinations are possible:
Doc Party Number

001 001 1
001 001 2
001 001 3

001 002 1
001 002 2
001 002 3
001 002 4

002 005 1
etc.

The catch is that the Number field must reset itself to 1 for a new combo or group of Doc and Party. Also, it would be great to make the Number field part of the PK instead of using an AutoNumber 'ID' field. Any code and comments would be GREATLY appreciated.

Thanks,
Eric

 

[tlbroadbent]

Check faq181-1179, faq181-1023, and faq700-184. You may find useful code there. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.