Using MS Access/Unique ID

QuestionQUESTION: Relatively new access user and I am trying to build a database from scratch for a small organisation and have to keep scraping it when a small issue pops up that I cannot manage and it becomes bigger than it is. Please help!

1. My first issue is creating a unique alpha numeric id that access generates for each new member added by the end user. I have reached the conclusion from reading your website that this does not have to be the primary key, but I am uncertain as to how to do and at what stage.

2. This number is important, like a student ID important. It will be assigned to each member and they will use this in filling forms etc. It will be used to run reports so members details are secure. For instance the number will be used to enter information, and then a button will be on the form that says "member details" where when clicked, the name and address of the member can be seen. My questions are -
i) Will this number be a field in the table? And which table? The member table or do I need to create a new table?
ii) Do I need to set up whatever is required for this at the beginning of the database before moving on to queries, forms and reports? Or is it a query?

3. I am trying to link the family members in the organisation, do I need to set up a relatives table and link it in the relationship diagram? Or do I include it as fields within the member table?

Thank you for your kind assistance! And direction will be greatly appreciated.

ANSWER: Let me ask a question first. Why do you need an alpha numeric unique ID? For example, in the US citizens are issued a Social Security Number this is a totally numeric number. So what does it matter what form this ID takes as long as it is unique? Yes, the ID can be used on forms. You can use it as a key field if you need to produce reports that are anonymous.

Generally I give each table an autonumber primary key. This key is used in all joins. Whether you decide to expose this value and use it as a visible identifier is up to you.

There are some situations where you want a some sort of ID that can contain info that identifies a record and, if you feel there is an need for that, I can show you how. But before you create another identifier make sure its justified.

As for the relatives table. Yes I would have a separate table that identifies each family member and their relationship to the member.

The unique identifier does not have to be alpha numeric, it can just be numeric, I do not need it to be long, I just need it to be about 3 or 4 characters. And then generate automatically for each new member added.

The unique identifier is to be given to all members so that their identity remains anonymous to people entering the donations and things of that nature data into the database. Hence the need for it to be a visible identifier.

I understand the primary key auto-number is useful, sorry if this is a silly point, but as I rebuild the database all new tables have a primary key auto-number that begins with 1 and I find that really confusing when trying to determine how various information can be pulled up using the auto-number PK if different items have the same ID.

For instance the donations table primary key starts with one and there are different types of donations, memberID also begins with the number 1, staffID, activitiesID etc - perhaps I am missing a point.

The new unique identifier will be used to pull the members information relating mostly to their donation history and current activities. And the database will also be used to pull reports on monthly or annual donations and there are different types of donations and other activities.

This is what makes me think the additional identifier is justified. It will only be used to enter/pull up information directly associated with whatever form/envelope the number is on that the member has filled out.

So, yes, please show me how to do that if you feel its is appropriate. Otherwise, is there another way I should/can approach this?

Also, in regards to the relatives table, I understand this to mean -members table, relatives table that just lists the different types of relatives (grand mother, dad) and then a third table that has both the relativeID and memberID as foreign keys. Is this right?

Your help is very much appreciated!

ANSWER: The thing you are missing is that a primary key is unique to a table. The value of that field is then used as a FOREIGN KEY (FK) in related tables. So lets take the example you gave of a Members table and donations table. The Members table looks like this:

So all those FKs in tblDonation will point to the corresponding record in the respective table. So you could actually have a record in tblDonation where ALL the FKs have a value of 1. There is nothing wrong with that.

So I don't see that you need anything more that the Autonumber MemberID to give to members.

And yes, to make sure you standardize the type of relative you would use a lookup table and include a RelativeTypeID as a FK to indicate the type.

I generally name my FKs the same as the corresponding PKs to identify the related table.

Based on your initial advise/recommendation of using the auto-number MemberID as the PK and identifier, I created a MembersDonations table, using the MemberID and DonationID as FK in the table. This table will be used to create a form to record donations. Is this right?

Also, I now have several look up tables: DonationType, Prefix, RelativeType etc. My question is would this lead to problems later?

I noticed you put StaffID and ActivityID in the donations table as a FK, I did not put these in mine as I am not sure about their use in this table. Would excluding them be considered poor design?

Finally, I have spoken to the group, and their preference would be to have the unique identifier as organizations of this type usually have a donation number which is basically what it will be.The number will be used to enter donations information not manage the database. So that the identity of the donor is restricted from end users inputting the record.

Their preference is not set in stone, and I continue to build it as above, however, could you please let me know how to create the unique numeric identifier and at what stage it is to be done? Is it a query? And do I have to do it before I start on the forms?

I would like to be able to also pull up the members donation history when the number is typed into a report or form, as in type in the number and it will show donation information by month or year and type depending on what the end user requires.

And then also be able to pull up the members information with the number only in regards to donations so as to be able to send letters referencing the donations and thank you letters.

Are these all various queries I create so that the end user only seeing the form can pull all this out? Could you please provide a guideline on how to do this as all the above information will still be required even if I do not create a unique identifier?

I used Access in another place and thought I would be able to build one myself for this group, so I really do appreciate your time and help on this. This website has been really informative.

Many Thanks!

AnswerYes, The member donations table will be a form to enter donations. I would use it as a subform to the member table, so you can see a member's details and their donation details.

No problem in having multiple lookup tables.

I was guessing on StaffID and ActivityID. I was assuming you needed to record the staff person who obtained or processed the donation. I also assumed that Activities were events that people donated for. Hence they would be attributes of the donation. If they aren't then certainly leave them out.

To create a unique identifier as you describe, see my blog on Sequential Numbering. It has details on how to do what you want. You will be able to create queries that include the identifier as a column and then use those queries for lookups and searches.