Primary Key Autonumber - Is it the best choice for discrete ID num

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sharon - 22 Apr 2009 23:06 GMT

Good Afternoon,

I have a database with one table called Referring Providers. I have a
control, "referring provider id" which I defined as an autonumber and primary
key.

Another table and form called "Daily Patient Visit Info" uses this
"referring provider id" so that each new "daily patient visit" also records
the "referring provider id". (And I am not repeating all the same information
in the table).

Question One: Primary Key Autonumber - Is it the best choice for discrete ID
number?

I am preparing my database to be used by up to 6 different users at
different locations - so I will plan on doing the front end and back end
solution.

Before I do that if possible I wanted to "clean-up" the autonumber primary
key field if it is the best choice moving forward. I have approximately 3600
records, with a couple of huge autonumber gaps, that I think were created
during some early update queries. I am not worried about the gaps themselves
but would like to enter less digits, the autonumber jumped to a 6 digit entry.

I did read a few of the solutions on the discussion groups and still thought
I would post. I did experiment with adding a new number field and renumbering
manually a couple hundred records changing from 6 to 4 digit numbers.

Question 2:
If autonumber/primary key is the best solution for this, I need to have the
new autonumber field start with record number 3860. What would be the
simplest way to do this?

Thank you!
Sharon

Reply to this Message

Sharon - 22 Apr 2009 23:43 GMT

I am using Access 2003, Win XP, SII Platform

> Good Afternoon,
>
[quoted text clipped - 31 lines]
> Thank you!
> Sharon

Reply to this Message

KARL DEWEY - 22 Apr 2009 23:57 GMT

>>would like to enter less digits

You should not need to enter any digits at all. The autonumber primary key
field should only be for the machine and not for human consumption.

Your [Daily Patient Visit Info] table should have a number - long integer -
field.

[referring provider id] should have a one-to-many relationship to [Daily
Patient Visit Info] 'id' field.

For data entry use a form/subform for Provider/[Daily Patient Visit Info]
with the Master/Child links using [referring provider id].

> Good Afternoon,
>
[quoted text clipped - 31 lines]
> Thank you!
> Sharon

Reply to this Message

boblarson - 23 Apr 2009 06:41 GMT

1. Why would you have a 4 digit Primary Key? That would give you the ability
to ONLY have up to 9,999 records.

2. Primary keys should really, in my opinion, be managed by the system that
uses them? They are really no good to the user other than the fact that the
SYSTEM uses them to tie together the records from one table to another (hence
the term RELATIONAL DATABASE) where one record is related to another.

3. If you choose an autonumber and make it the primary key and let the
system deal with the "behind the scenes stuff" it will make the job of
Referential Integrity a bit easier to deal with. But you can still put
things in place like a Multi-Field index to keep entries from being made as
if you had a composite key (for example not being able to have more than one
entry per date of a certain item).

Signature

Bob Larson
Free MS Access Tutorials and Samples at http://www.btabdevelopment.com

__________________________________

> Good Afternoon,
>
[quoted text clipped - 31 lines]
> Thank you!
> Sharon

Reply to this Message

Ken Sheridan - 23 Apr 2009 18:36 GMT

Sharon:

There appear to be two separate issues here:

1. Do the 'referring provider id' values have any significance other than
as unique identifiers, e.g. do they have some ordinal significance? This
does not seem to be the case as you say 'I am not worried about the gaps
themselves'. In which case the values, and hence the number of digits are
irrelevant, as both are arbitrary. Bear in mind that in a well designed
application the user will never see these values; they will happily do their
job behind the scenes.

When you talk about entering 'less digits' you presumably have in mind when
entering the foreign key values into the 'Daily Patient Visit Info' table.
Again you do not have to enter these values. In a form based on this table
you can use a combo box set up as follows:

ControlSource: [referring provider id]

RowSource: SELECT [referring provider id], [referring provider] FROM
[Referring Providers] ORDER BY [referring provider] ;

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

The user can then select the referring provider by name, but the value of
the combo box, and hence the referring provider id field in the underlying
Daily Patient Visit Info table will be the numeric value which corresponds to
the value of the primary key column for that provider in the Referring
Providers table.

2. The second issue is that you plan to distribute the database to up to 6
different users at different locations. The question here is whether they
will all be accessing the same back end file remotely, or will they each have
their own local copy of the back end file? If the former then there is no
problem with using an autonumber primary key; if the latter however, this
will give rise to problems if you need to synchronize the various back ends
as the same autonumber values are likely to have been generated by the
multiple users if they add new providers. This is not a problem as far as
the Referring Providers table is concerned as you can simply append the (up
to) 6 sets of data less the referring provider id column into a new 'master'
table in which new primary key values are generated in an autonumber column,
but in the case of the Daily Patient Visit Info table the referring provider
id values will now no longer reference the correct rows in this 'master'
table.

You have several options in this case:

1. Do not allow the remote users to manually insert new rows into the
Referring Providers table, but only do this in the 'master' table. To
transfer any new rows from this to the remote Referring Providers tables
would then require an 'append' query as this can assign the values from the
autonumber column in the 'master' table to the same column in the remote
tables rather than leaving the system to generate the values as would be the
case with manual insertion of new rows.

2. Do not use the autonumber column as the primary key, but instead use it
as one part of a composite key. You could then differentiate each of the
remote tables by means of an additional column, e.g. a 'location code' letter
from A to F (and beyond if more than 6 locations are later used). In this
case the primary key of Referring Providers would be a composite one made up
of the two columns, the letter and the number. You would then require a
similar two-column foreign key in Daily Patient Visit Info. For each
location you could set the DefaultValue property of this 'location code' in
both tables to the relevant letter for that location, so its automatically
entered.

Ken Sheridan
Stafford, England

> Good Afternoon,
>
[quoted text clipped - 31 lines]
> Thank you!
> Sharon

Reply to this Message

Sharon - 27 Apr 2009 22:36 GMT

Hi Ken,

Thank you for you thoughtful reply. I will divide my answer by numbers too.
So, for 1) There isn't any significance to the number other than it needs to
be a unique identifier. Although my users won't see this number, I do. I have
been looking at it for the past year and would just like it to be remotely
similar to the actual number of "referring providers" it represents and not
many times higher. (4,000 vx 600,000)It sounds like I should give this up -
It's just I see it and it bugs me as it represents an early mistake. Although
there will be some change in the referring provider group, people leaving the
area, changing address, etc - it is less likely that this group will
experience growth, or a increase in the number of records in the referring
provider table. Even so, sounds like with autonumber I could have nearly
double the number of providers we have now without running into problems.

1a) I did use a combo box in my design last year, and it has worked well.
The user looks up the Last name of the referring provider, and can find the
rest of their key contact information via the combo box which then autofils
the id number and a few other important identifiying pieces of information I
track. As you an others have mentioned, none of the users will see or need to
see the referring provider id number)

1a-i ) When they can't find their provider name in the combo box of the form
"Daily Patient Information", they click on a button link them to the "add new
referring provider form", once they save their addition they can go back to
the form and the entry they were working on and see it listed in the combo
box and add it to the record they are working on.

1b) The reason why I am manually cuting and pasting id numbers has to do
with some other folks who are not yet using the database, we are importing
the information from excel - this will not be continuing and is not too
important.

2) I was planning to have all six users use the same back end database -
which is on a common server and install a front end on their local individual
desktops. So, if I am understanding you, I should do fine continuing with the
autonumber. Am I right?(I will certainly save your suggestion should I need
to change and install a back end at each location. I am doing a trial with
one user now and the data looks good.)

Thanks again Ken- Would you be willing to take on a few questions or
critique my design overall?

Sharon McCallum, PT, DPT
Seattle WA

> Sharon:
>
[quoted text clipped - 107 lines]
> > Thank you!
> > Sharon

Reply to this Message

Ken Sheridan - 28 Apr 2009 12:04 GMT

> Hi Ken,
>
[quoted text clipped - 153 lines]
> > > Thank you!
> > > Sharon

Reply to this Message

Ken Sheridan - 28 Apr 2009 12:55 GMT

Sharon:

Sorry about the empty reply!

As your users are all accessing the same back end then an autonumber is
fine, and would be my preferred option. If there were a real need for the
numbers to be sequential then an autonumber is not suitable and you'd need to
compute the numbers yourself when inserting rows into the table, but it
doesn't sound like this is the case. If you feel that the messy nature of
the numbering offends your aesthetic sensibilities too much, what you could
do is create copy of the table's structure and in the new table. It goes
without saying that you should back up the database first before doing this
of course:

1. Change the name of the autonumber column to a new name.

2. Add a new number column of the same name as the original autonumber
column, but make this column a straightforward number data type, not an
autonumber.

3. Execute an 'amend' query to insert the rows from the original table into
the new one. The new autonumber column will be automatically filled with a
new sequence of numbers.

4. For any tables related to the original table on the autonumber column
create an 'update' query which joins the other table to the new copy of the
original table, but this time on the new non-autonumber ID column. In the
query update the foreign key column to the value of the new autonumber column.

5. Once you are satisfied that everything has been updated correctly, delete
the original table and rename the new copy of it to the original name. In
the new table delete the non-autonumber column and rename the autonumber
column to the original column name. Finally recreate the relationship
between the two tables.

As regards the combo box of names a button to add a new record via another
form is fine, but I'd recommend that you open the other form in dialogue
mode. This has two effects: (a) you have to close the other form before
returning to the main form; (b) code execution in the calling procedure is
interrupted until the other form is closed, so you can then requery the combo
box to show the new name. The button's code would thus be along these lines:

DoCmd.OpenForm "YourFormName", _
DataMode:=acFormAdd, _
WindowMode:=acDialog

Me.YourComboBoxName.Requery

You can automate it even further if you wish by putting code in the combo
box's NotInList event procedure. This allows you to type the new name
directly into the combo box rather than using a separate button. The
following example is the code for a combo box's NotInList event procedure for
adding a new city in this way:

Private Sub cboCities_NotInList(NewData As String, Response As Integer)

Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list?"

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmCities", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure frmCities closed
DoCmd.Close acForm, "frmCities"
' ensure city has been added
If Not IsNull(DLookup("CityID", "Cities", "City = """ & _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Cities table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If

End Sub

In the frmCities form's Open event procedure the following code assigns the
new city name to the City control as its DefaultValue property ready for the
user to enter further data such as the state in which the city is located:

Private Sub Form_Open(Cancel As Integer)

If Not IsNull(Me.OpenArgs) Then
Me.City.DefaultValue = """" & Me.OpenArgs & """"
End If

End Sub

Don't hesitate to post back with any other questions you might want me to
have a stab at. If you start a new thread I can't guarantee I'll see it, but
you can always mail me at:

kenwsheridan<at>yahoo<dot>co<dot>uk

to point me in the direction of a particular thread if you want to draw my
attention to it.

Ken Sheridan
Stafford, England

> Hi Ken,
>
[quoted text clipped - 153 lines]
> > > Thank you!
> > > Sharon

Reply to this Message