Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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 39

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


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

604 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