Preventing Duplicates

[Nick]

I have a table for members of my club. Each record has a
unique ID, (Auto number); A membership number; and Status,
plus other details.
The statuses are, A for Achieved, F for Financial and N
for New members. New members may or may not have member
number assigned to them.
The membership numbers are re-used once a record has its
status set to A. This keeps our membership numbers to a
minium.
What I would like to do is prevent duplicate records using
membership number and the status fields where the status
is anything but A. Duplicate records where the membership
number and status is set to A are acceptable.
Point to note:
Is that sometimes it is necessary to re-activate an record
by changing its status back from A to one of the other
statuses. If the membership number has been re-assigned
then I will need to be made to set an un-used number
before exiting the record.

This is a big ask and I would appreciate any help

 

[Jeff Boyce]

Nick

I'm not sure I understand the business need for reusing membership numbers
.... doesn't that cause confusion when two folks share the same number?

If you are saying that the combination of MembershipNumber and Status cannot
be duplicated, you can create an index on the combination of these two
fields, and dis-allow duplicates. Open the table in design view and open
the index window. Select the two fields, give them an index name, set No
Duplicates.

Good luck

Jeff Boyce
<Access MVP>

 

[Tim Ferguson]

What I would like to do is prevent duplicate records using
membership number and the status fields where the status
is anything but A. Duplicate records where the membership
number and status is set to A are acceptable.

An alternative approach would be to maintain a table for allowable
MembershipNumbers with just the single field.

MembershipNumbers(
MembershipNumber LongInt Primary Key,
)

In the members table, you fix the MembershipNumber field as a FK
referencing this new table, and give it a Unique Index with Ignore
Nulls=True, and make it non-Required -- in this way you can have none-or-
one member per number and none-or-one number per member.

Finally, to fix the Status field, you can have Table-level validation rule
like

(Status<>"A") OR (MembershipNumber Is Null)

Of course, you need to stick a certain amount of code behind the form to
prevent the user ever triggering these safeguards, but that is another
story!

Hope that helps


Tim F