Primary key

[John Gavin]

We use our primary key in our membership database to keep
track of how many members we have at a glance. When we
delete members it will delete the record but we then have
numbers that are not assigned to any member thus giving us
an incorrect total number of members. How can we change the
primary key numbers to renumber in order?

 

[Ken Snell [MVP]]

I assume you're using an autonumber field as the primary key? If you want
meaning for that field, you should not use an autonumber field .. not only
does it do what you describe, but its "next" values can become random or
even negative.

Also, changing the value of a primary key isn't a practice that I recommend
you do regularly. If you want to know the number of members, just run a
query that returns the count and display that result.

Otherwise, if you want to change the primary key's values, you must change
it from an autonumber field to a Long Integer field. Be sure that all
relationships have referential integrity established and that you have
Cascade Update set for all the joins from this parent table to all the
children tables so that the changes will propogate to all chidren records.
Then you'll need to write an update query that will change the value of the
primary key (which record should be given the value of one, which gets two,
etc.?) without creating any duplicates in the table while the update query
runs. And then you'll need to run this query every time you need to reassign
numbers. Oh, and you'll need to have your "new member" form automatically
calculate what the next number is for the next new member and assign that to
the primary key when that record is being entered.

Are you sure you can't just run a query to tell you how many members you
have?
SELECT Count(*) FROM TableName;

 

[John Vinson]

We use our primary key in our membership database to keep
track of how many members we have at a glance.

Well... Don't.

That's a completely improper use of a Primary Key.

Think about it: suppose you have 3218 members (some years from now
when your organization is succeeding really well). You have a table of
members, linked to thirteen other tables of activities, membership
fees, payments, mailings, etc. etc.

Member 2, rest his soul, dies.

This means that you must change the MemberID in 3216 records in the
membership table, and in maybe tens of thousands of other records in
other tables. You must also inform each member "Your memberID,
formerly 3114, is now 3113; please write this number on any checks in
the future". You must also erase the membershipID on every piece of
paper, and every human cerebellum, where it is noted.

All to get a number which a very simple

DCount("*", [Members])

expression can give you!


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps