Access primary key auto-numbering malfunction

Thank you all for your input.  I feel much better knowing I do not have to worry about the randomness of the primary key number.

Let me give you an over view of my situation and please freely offer advice and direction for improving the coordination of my data. 

The records I have are municipal cemetery records - individual demographics, burial location, deeds issued, monuments installed/photos of such, other legal transactions such as assignment/transfer/re-sale info, along with pdf document scans etc.

Currently I have 3 Access files, some with multiple tables and each with many queries and/or reports.  Currently those reports are specific within one access file but are not related to the other files.  The three files are:

    1.  Allburials = Name, death date/place, birth date/place, burial location, registry#, next of kin, military info, funeral director, information notes, type of interment/disinterment (21,000 + records)

    2.  Deeds = Name, burial location, date of purchase, cost, assignment/transfer/re-sale information and dates, registry #, monument info  (eventually I hope to add fields for pdf scans of these documents and pictures of the monuments into additional fields) (approx. 15,000 records)

   3.  Pre-needs = Name, burial location, who is meant to use location  (approx. 3,000 records)

My thought process was this:

   1.  I knew the main data file (Allburials) would be very large.  The "registry #" would eventually be the primary key field, however, I could not make it a primary key at the time of creation or even now because of duplicity.  I have approx. 500 records that do not have a unique registry # but instead that field is noted with "Ledger".  This means that these records are pre-1900 and pre-issuance of an actual burial registry permit.  These records are simply a written line in a historic ledger book.  

   2.  My original idea was that each access file would contain the "registry #" and would therefore allow me to build relationships that would tie all the data together for reporting purposes.  That, however, is not going to work.  I also cannot use the burial location as a primary key because multiple people are sometimes buried in the same location.

So, I had not assigned a primary key because I could not decide what that common key should be between the three access files.  I did not place all the information into separate tables within one access file because I knew it would be too large and I was concerned over stability of the files.  We are a satellite office from the city's main servers and have regular issues with speed and connectivity.  I knew eventually I would want to connect the three access files for reporting purposes but have not reached that state yet.  It was one of our new IT personnel that required me to add a primary key field at this time to each table.  When I added that field it assigned a chronological, unique number to each but then as I continued to add new records, the number became extremely random which made me concerned.

It has been suggested by others that all this information be moved out of Access and into Sequel but I have little to no experience with that or what it would entail to do so.

Thanks again everyone for your willingness to offer helpful suggestions.  I know enough "to be dangerous,"  so to speak, but hardly enough to comfortably use the program to its potential.

First, your database should be split into a back end (tables) and a front end (everything else), you can have multiple back ends if it gets too large. You can also have multiple front ends if you want one application to have different functionality.

You are limited to a maximum of 2Gig in file size. Can you check the size of all your files and see what that totals to?

Second, yes you should have had a primary key from the beginning. A relational database requires that you use unique identifiers. Even if you did not have some natural key or combination of field to create a key, you should still have used an Autonumber.

Your main problem now, is that, even though you have added a Autonumber PK, because you didn't have a PK in the beginning, you have nothing to tell which child record goes with what parent. Before you can go any further, you need to make sure every table has a Primary key and that every table that relates to another table has a foreign key that identifies the related record.

Is this a multiuser database?  Is it split?

When was the last time the BE was compacted?

Never forget, AutoNumber have but one role and that is to provide a unique identifier for each record.  There is no guarantee that Auto Numbers will be sequential!  If you need sequential numbers, then you need to create your own routine to do so, for instance:

=Nz(DMax("YourField", "YourTable"),0)+1

The following article may also be informative to you: http://allenbrowne.com/ser-40.html

Hi, 

check on your table in design mode the property of your counter field (increment/random).

Mimmo

If there is a real need for sequential numbering you must compute them yourself, an autonumber is not guaranteed to do this.  You'll find an example as CusomNumber.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

However, it seems that your only purpose in introducing the autonumber column was to provide the table with a key, in which case the values are irrelevant as they will never be exposed to users.  The fact that you appear to have been operating this database without this table having a key does make me wonder how data in it were referenced?  One of the fundamental rules of the database relational model (Codd's Rule #2) is the Guaranteed Access Rule:

Every datum (atomic value) in a relational database is guaranteed to be logically accessible by resorting to a combination of table name, primary key value and column name.

Does the fact that your refer to the table as a 'database' mean that you have only one table in the file?  If so it almost certainly is in need of normalization by decomposition into a set of correctly normalized tables.  A database is almost without exception a set of such tables.