Member Numbers

[Nick]

I need to re-use member numbers for my club. The database
that I have set-up, has an auto number primary key for
each member and each member is issued the next available
lowest member number. As members becomes A = Achieved or X
= Declined, their numbers become available for re-use.
They retain their member number so that if they are re-
instated and their original member number is available for
re-use. So if a member is F = financial, N = New, NF = Non-
Financial, I need to prevent any new members or re-
instated A or X members from being issued a duplicate
member number.

Can anyone give me some direction on how to overcome this
problem?

All help appreciated
Nick

 

[Jamie Collins]

As members becomes A = Achieved or
X = Declined, their numbers become available for
re-use.

A typo? Perhaps it should have been, "A = Archived"? Otherwise, I
don't see how the member number for someone who has achieved membership
can be available for re-use.

They retain their member number so that if they are
re-instated and their original member number is
available for re-use.

Another typo? Perhaps it should have been something more like, "They
retain a note of their original member number so that if they are
re-instated and their original member number is currently unallocated
then it is re-used for that member." If they retain their member number
then by definition it is not available for re-use.

I think you would get a better response if you re-posted with clearer
definitions for your business rules. In doing so, you may find the flaw
lies in the rules themselves rather than your database design.

Jamie.

--