Jump to content

Unique membership number

This topic is 5767 days old. Please don't post here. Open a new topic instead.

Recommended Posts


I'm currently creating a membership database.

When updating a data for a member, the database creates a scrollable 'history' of all the changes that have taken place for that member. This is like a snapshot of what the member's info was when info was updated/changed. (This needs to be like this for the purposes of the database - the users need to have this history data available).

I also have an auto-generating membership number, for when a new member is added.

However, the problem i am experiencing, is when i update a member's info and the database creates a new 'history snapshot' - it gives the new history the next membership number, rather than using it's original one.

Can anybody suggest a way that i can make the database use exsisting membership numbers when updating member info, and only use a new membership number when a new member is first added..?

I have attached a copy of the database.

I have put in two example members - The existing membership numbers for these are 1 and 2 (should be just 1), and 3 (should be membership number 2).

Many thanks for your time.

Kindest Regards,



Link to comment
Share on other sites

Hi Shane

The problem here is that you are auto-numbering the records even though some of the records refer to the same member. In other words the record numbering is going to clash with membership numbering. Obviously with small numbers this is manually adjustable but once you have large numbers of records it will get out of hand.

I think what you need to do here is to have an Auto-entered serial number for the record ID and then a different style of auto-enter for membership numbering.

For the updating of the membership number can I suggest:

1. Make a new table occurrence of the membership table, call it MembershipNumberControl.

2. Relate it to your existing membership table via record number using the cartesian join. In the relationship dialog sort the MembershipNumberControl records by Membership number Descending. This ensures that the first related record by this relationship has the highest membership number.

3. Assuming that History records are obtained by duplication such a record will already have a membership number so only new records will need on. So do an auto-enter on membership number

If(Membership Number = "";MembershipNumberControl::Membership Number + 1;Membership Number)

This produces a new membership number one higher than the previous highest but does nothing if there already is a membership number.

I have altered your sample - attached.


Link to comment
Share on other sites

This topic is 5767 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.