Solved

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

Posted on 2015-01-23
4
265 Views
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.
Append-Query-Design-View.PNG
Append-Query-SQL.txt
0
Comment
Question by:David Bigelow
  • 2
4 Comments
 
LVL 18

Assisted Solution

by:SimonAdept
SimonAdept earned 167 total points
Comment Utility
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.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 167 total points
Comment Utility
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:
Bob=1
Sally=2
Greg=3
Betty=5
Tom=5
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)
:-O

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
;-)

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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.
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 166 total points
Comment Utility
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.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now