Deciding on a primary key

[lchiav]

I need to assign an ID field to our membership database. Membership is divided into chapters. I assigned each chapter a number. Is there a method for selecting the unique field for my primary key. I was considering 01-AB-XXX. 01=Chapter, AB=First two letters of members last name, XXX - sequential number. It seems like I would have to keep track of each chapter's number this way.

 

[Quehay]

You should probably establish a distinct table for each chapter.&nbsp;&nbsp;Use autoassign for the number, even though you'll just have two tables (at the moment. . .).&nbsp;&nbsp;In each table you can have fields that pertain to each chapter:<br><br>Ch ID#<br>Address<br>Chairperson<br>President<br>Whatever<br><br>Then when you are entering your members into their table you will use autolookup to assign the ChapterID as a foreign key in each member record.&nbsp;&nbsp;Use an autoassign number for the member ID as well, as there can be unforeseen complications in using a meaningful index as the primary key.<br><br>Wait to see what the other, more experiencedcontributors have to say. . .

 

[DougP]

You may be able to use one table to keep track of the numbers portions.<br>And this table would only have one record. Which would be constantly updated.<br>So the fields of this table would be<br>XXX - sequential number<br>Chapter01<br>Chapter02<br>etc.<br>And the table might look like this<br>sequential number Chapter01 Chapter02 Chapter03<br>214 12 4 17<br>Then use the following code to increment the number in each field.<br>-----------------------------<br>'open table and get the last sequential number used<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim MyDB As Database, MySet As Recordset<br>&nbsp;&nbsp;&nbsp;&nbsp;Set MyDB = currentdb<br>&nbsp;&nbsp;&nbsp;&nbsp;Set MySet = MyDB.OpenRecordset(&quot;sequential number&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MySet.MoveFirst<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MySet.Edit<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Temp1 = MySet.Fields(&quot;sequential number&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Temp2 = Temp1 + 1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MySet.Fields(&quot;sequential number&quot = Temp2<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MySet.Update<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SequenceNum = Temp1 <br>&nbsp;&nbsp;&nbsp;&nbsp;MySet.Close<br>&nbsp;&nbsp;&nbsp;&nbsp;MyDB.Close<br>--------------------------------------<br>Ok, so this little function would be needed to increment each field.<br>I showed how to increment just the sequential number (SequenceNum) field<br>You could modify this code to be a generic function in which you pass the field and then return the Value of that field.<br><br>

 

[Amiel]

Ichi ;<br><br>Why not use the 'autonumber' capability in Access. This <u>guarantees that the value (a number) will be unique in the table</u>. From this point you'll need to guarantee that each Chapter is entered only once in that table. Try using the chapter name or zip code or phone number and set this field to <u><b>indexed yes no duplicates</b></u>. This guarantees that you dont end up / with a chapter having more than one entry in your [chapters] table. vis -a -vie each chapter has a unique key value.<br><br>From this point you have the unique key for each chapter. When you assign the value of a chapter to a member you use the (guaranteed to be) unique autonumber from the chapters table.<br><br>This isn't as 'imaginitive' as some other solutions but it's what is used to get the job done. It's also the reason that Microsoft added the 'autonumber' capability to JET Databases.<br><br>NOTE: If a user can belong to multiple chapters you can use a table to store the members/chapters entries. Or store it in one field delimited with semicolons (my preference as you can feed this field directly to a listbox). <b>Don't use 'Composit Indexes'</b> as you seem to be suggesting, although imaginitive, It'll only makes managing&nbsp;&nbsp;things more difficult! <br><br> <p>Amiel<br><a href=mailto:amielzz@netscape.net>amielzz@netscape.net</a><br><a href= > </a><br>

 

[Guest_imported]

Doug,<br><br>Your solution seems more than a little baroque to me, yet there may be benefits that I don't see.&nbsp;&nbsp;Why would you implement this solution rather than just using Autonumber?

 

[elizabeth]

Of course, if you could get the user's Social Security Number, you'd be all set!<br><br>Barring that, I would tend to agree with Amiel, but would go even further and use AutoNumber for ChapterID too. Using Autonumbers makes an application much easier to maintain than using &quot;smart&quot; IDs. I just use the Lookup Wizard in table design view to show the name, short name or other identifier to make it easier for the user.<br><br>

 

[lchiav]

Thanks for the help. I am going to try Amiel and Elizabeth's suggestions.