Member Numbers

  • Thread starter Thread starter Nick
  • Start date Start date
N

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, it
may be re-used. There could be multiple A or X members
with the same member number. 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
 
First, while the member numbers "may" be reused, does that mean they "have"
to be reused? If not, then the easiest fix is to not reuse them.

However, since you probably already have some that have been reused, you
need to recreate the primary key for the table if it is currently the member
ID field. The primary key needs to be unique. You can combine more than one
field to make it unique. So, by your description, while the number may be
repeated. A+number or X+number should always be unique. If you make both of
these your primary key, this will eliminate the problem. Also, if you try to
change an X back to an A and someone else has the number, then you will get
a duplicate index value error. If you have a different field as the primary
key, you can still set a unique index over more than one field. To do this,
open the table in design view. Go to View|Indexes. Place a name for the
index in the Index Name box. Enter the first field name in the Field Name
box and set the Sort Order. To add the next field to the same index, go to
the next line, leave the Index Name box empty, and fill in the Field Name
box. Once you have done this, go to the bottom of the window and set Unique
to Yes.
 
PS.

When setting up the multifield index, you have to be on the first field to
get the Unique option to show at the bottom of the window.
 
Thanks for your answer Wayne,
But the first part of your message is unavailable Could
you please re-post it.

Nick
 
Wilco.

First, while the member numbers "may" be reused, does that mean they "have"
to be reused? If not, then the easiest fix is to not reuse them.

However, since you probably already have some that have been reused, you
need to recreate the primary key for the table if it is currently the member
ID field. The primary key needs to be unique. You can combine more than one
field to make it unique. So, by your description, while the number may be
repeated. A+number or X+number should always be unique. If you make both of
these your primary key, this will eliminate the problem. Also, if you try to
change an X back to an A and someone else has the number, then you will get
a duplicate index value error. If you have a different field as the primary
key, you can still set a unique index over more than one field. To do this,
open the table in design view. Go to View|Indexes. Place a name for the
index in the Index Name box. Enter the first field name in the Field Name
box and set the Sort Order. To add the next field to the same index, go to
the next line, leave the Index Name box empty, and fill in the Field Name
box. Once you have done this, go to the bottom of the window and set Unique
to Yes.
 
Back
Top