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,

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