Unique Member Number - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Unique Member Number

Duchesne Claude-Real

3 years ago

Hello everyone

after having read several "posts" on the access forum I can't find an answer to my question.

I have a "personT" table and also a MemberT table. here the member number must be "Unique" and never be used again in the future in case the person leaves or dies. His membership number is erased. (example, member number 1,2,3,4,5,6 and therefore member #6 cancels his subscription so there are 1,2,3,4,5 left. and Later a new person subscribes, then his number will not be 6, but #7 which gives (1,2,3,4,5,7) In other words this field works exactly like the auto/number primary key field.

I have already done this manipulation but in Excel. I copied the member number cell to another place (Z100), and the formula for adding a member was cell Z100 + 1 which automatically gave the next number even if the member had been deleted between time.

I don't know if I'm clear, with google translate? But I don't see how to do this in access.

I can't take a variable? because when the software is closed and then reopened the data is lost?. So I'm thinking of adding a field in a "helperT" help table and copying the number of the last member to add there. ?

Am I on the right track? or do you have an explanatory video on this or which if looks like because I found nothing.

THANKS

	Dan Jackson @Reply 3 years ago
You can use auto numbers in Access - these will always count up and NEVER duplicate Alternatively, you can use your own Counter and set it to Indexed (No Duplicates)

	Duchesne Claude-Real OP @Reply 3 years ago
Thank you Dan I didn't think that we could put 2 "auto/number" fields and that there was 1 which would be the primary key. I'll give it a try, your answer and the link gave me new options to try. A+

	Scott Axton @Reply 3 years ago
Just to be clear - Access only allows one Autonumber field per table. Also, you can only designate one field as the primary key unless you have a composite key (Covered first in Access Expert 23 ). You can only have one Primary Key per table. What Kevin was referring to was that you can create your own Counter field. Alternatively you could create your own Customer Codes.

	Scott Axton @Reply 3 years ago
Sorry I said Kevin I meant Dan

	Kevin Robertson @Reply 3 years ago
Scott It is possible to have multiple fields set as the Primary Key. If you select two fields and click the Key button on the ribbon those two fields will be the Primary Key for that table. I think similar to a Composite Key. I've never needed to do this, maybe Richard could give some insight on this and why and when you would set up the Primary Key in this way.

	Scott Axton @Reply 3 years ago
Kevin you are absolutely correct. BUT... If you highlight the field 1 and 2 and make them the Primary Key then Access makes a Composite Key out of it adhock. Then again if you try and make "IDField" - Primary Key - AutoNumber and add a "SecondKey" Field as AutoNumber Access will not allow it.

	Scott Axton @Reply 3 years ago

	Scott Axton @Reply 3 years ago
So I stand by my post. You can only have one AutoNumber key per table. One field as the primary key unless you have a composite key

	Richard Rost @Reply 3 years ago
I don't understand why you have to have a separate person table and a member table explain that relationship to me.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View