I have a table of members

Each member belongs to a chapter

They have a membership #

If the member is in chapter 172

Then the first member # is 1720001 and so on

I would like to auto enter membership numbers when I enter a new member

What calc would work here?

It would have to find the highest number used first, within the chapter, then add 1

If I used something like max (membership#) + 1, it would give me the highest number used by all chapters.

Thanks, Dave

How necessary is this?

If this membership# is a relational key, I'd suggest you use plain old serial numbers, ignoring the Chapter. If you wish to see the Chapter, you can always place the Chapter# on the layout next to it. My reasoning is:

1. Using a conditional serial# unnecessarily complicates the system and introduces the possibility of duplicates.

2. If a Member changes Chapters, you then have to either change their Membership# in all relevant records or accept that some members have numbers starting with a different Chapter# (in which case the usefulness is lost).

You make a good point. I should have questioned the logic in the first place. I am going to suggest they change their format.

Thanks Dave