Should I be concerned about the gap in primary keys created when running an append query?

Posted on 2015-01-23
Last Modified: 2015-01-28
When I run an append query to update employees' skills based on attendance in a class in which certain skills were taught, multiple records match the initial query, but the records that need to be updated are relatively few. So, for 1,500 records matching, perhaps only 20 employees, the ones who attended the class, will be updated. The rest of the rows of records of the other unmatched rows are "deleted," for lack of a better understanding or description, so that there are multiple large gaps in the auto numbered primary keys of the training records. As I add training records, these gaps grow quite a bit wider, from the 100s to now, the 1,000s, and soon, the 10,000s.

I'm not sure if I'm describing very well, but it's the difference in the record amounts indicated by the dialog boxes while appending information.

Should those "missing records," or gaps, be a cause of concern as long as the data I want to be saved is being saved? Which, it is being saved. It just seems that the autonumbered primary key is going to become huge, quickly. Is that a performance hindering circumstance?

Perhaps there would be a better way to write my append query. One thought was to exclude records that didn't have a training date within the last X amount of days, but that would mess things up in case someone needed to update records for training prior to that time. I've attached a design and sql copy of my append query.
Question by:David Bigelow
  • 2
LVL 18

Assisted Solution

Simon earned 167 total points
ID: 40567596
Generally speaking, gaps in the auto-numbering don't matter at all or affect performance. The only time you'd worry is if it looks likely to exceed 2,147,483,647 (the max value for the long integer datatype).

You probably could improve the design on the process that you're using to udpate the training records, but I couldn't be sure from what I've seen. Ideally, you would only add rows for the people that need updating, perhaps by using an intermediate table to do the picking of the 20 out of the 1500 and then just pushing that 20 to the final destination table.
LVL 74

Accepted Solution

Jeffrey Coachman earned 167 total points
ID: 40567830
It's typically is not worth the trouble to fix gaps in a "production" database.
Lets suppose that you have 5 customers with ID's of:
Now cust 3 (Greg) is deleted. (By accident or intentionally)
Now you have a "missing" value.
Now you need to create a system to "locate" all the missing numbers and fill them in first (before adding a new, automatically incremented number).
So using an AutoNumber filed becomes impossible (with this logic needed)
...and now you need to create an automated system to fill in the holes and deal with all of the related records (and add error handling, rollbacks, validation, ...etc)

Even if you did replace Cust3, with lets say "Adam", then what happens to all of the Orders that have 3 for greg's CustID??
...Now what if Greg comes back?
Does he get his old number back? just gets too messy.

A Primary key will just guarantee uniqueness, ...not uninterrupted sequential values.
The user may not ever see the autonumber (you can create a CustomerID of an AutoNumber, ...but also have a CustRefID that is a concatenation of the CustomerID and some other value(s) the "gaps" will only be noticeable by you.
At some point (after designing many tables in you DB), will not be worth the trouble of trying to order all of the tables over and over again,...
But worse, creating countless Update queries to shift related records around.

Now, for a "Test" database, things are different.
You can do all your testing and leave all the gaps,...
Then right before you "distribute" the app, can:
Delete all the test data/records
Delete all the
Insert new autonumber fields (thus restarting all of them all to 0)
From that point on, would have no real need to worry about "holes" in your autonumbers.

It just seems that the autonumbered primary key is going to become huge, quickly.

Not likely... at the autonumber theoretical limit of 2 billion(2,000,000,000) numbers, ...even if you created 250,000(quarter million) records *per day*, ...*every day*, for *20 years*, you would still only be at  1,775,000,000

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40567831
corrected paragraph:

Now, ...for a "Test" database, things are different.
You can do all your testing and leave all the gaps,...
Then right before you "distribute" the app, can:
1. Delete all the test data/records
2. Delete all the existing Autonumber fields
3. Insert new autonumber fields (thus restarting all of them all to 0)
From that point on, would have no real need to worry about "holes" in your autonumbers.
LVL 35

Assisted Solution

PatHartman earned 166 total points
ID: 40569472
If the append query is creating gaps, it is because not all rows are being appended.  Some are being discarded probably due to key violations.  I would change the query to stop appending duplicates if possible.  To do that, you will have to join to the target table using a left join and only select rows where the id of the target table is null - that means that what you are appending is not already there.

Although the gaps in the autonumber won't affect anything, having them grow by 10,000 every time you run the append will make the autonumber grow huge in short order.

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Calculation in Access 5 26
Microsoft Access - Stopping Control - (minus) 4 32
aggregate query? 20 50
Progress bar in access form 10 17
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question