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

Posted on 2015-01-23
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 18

Assisted Solution

Simon earned 668 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 668 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?
...it 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)
...so the "gaps" will only be noticeable by you.
At some point (after designing many tables in you DB),...it 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, ...you 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, ...you 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, ...you 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, ...you would have no real need to worry about "holes" in your autonumbers.
LVL 38

Assisted Solution

PatHartman earned 664 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

762 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