Solved

Append Query  adds to table, but assigns random autonumber instead of next consecutive number in "append to" table

Posted on 2016-10-06
5
63 Views
Last Modified: 2016-10-10
I am working in an existing Access 2013 database which is designed to copy the budget items from a budget table and append them back in with the next fiscal year.  Once the append query is run, the data fields append ok accept for the auto-number key field.  Is there any code  to add to the SQL to have the records append and begin the next consecutive number available in the "append to" table.

The SQL Viewer reads:

INSERT INTO tblBudget ( [Sub Acct Numer], Description, [Module], Task, [Qty Req], [Unit Cost], [Cost Center], [Account Number], Fund, Project, DateEntered, [Budg Year] )
SELECT tblBudget.[Sub Acct Numer], tblBudget.Description, tblBudget.Module, tblBudget.Task, tblBudget.[Qty Req], tblBudget.[Unit Cost], tblBudget.[Cost Center], tblBudget.[Account Number], tblBudget.Fund, tblBudget.Project, Date() AS DateEntered, [Budg Year]+1 AS [New Budget Year]
FROM tblBudget
WHERE (((tblBudget.[Budg Year])=16))
ORDER BY tblBudget.[Account Number], tblBudget.Fund, tblBudget.Project;

Open in new window

Autonumber-issue.docx
0
Comment
Question by:Tammy Allen
[X]
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
5 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 41832114
Before we give you a literal answer ... Why are you dependent on the AutoNumber value being a specific value?  Generally these are used only for uniquely assigning a row and for PK-FK's, and to not have any embedded 'business logic' as to their meaning.  

Many developers in this situation will create another column as a 'business key' that will be the unique identifier exposed to users, and the app can contain whatever funky logic is used to create that number.  Especially for situations where there are mixed letters and numbers 2016A1, or if part of the value has an exact meaning like 2016=the year.
0
 
LVL 37

Accepted Solution

by:
PatHartman earned 250 total points
ID: 41832220
The "Increment" setting does not generate random numbers.  If yours is, then your database is corrupted.  Each new autonumber should be the previous value +1.  You will occasionally run into gaps.  If you run an append query but cancel it, all the autonumbers have already been generated so there will be a gap.  If you delete records, there will be a gap.  For some versions of Access, compacting the database will get rid of phantom ending numbers so if your high id is currently 101010 and you run an append query to append 5 rows but cancel it, technically the next available number is 101016.  to get it back to being 101011, try compacting.  Compacting won't remove internal gaps,  It only removes ending phantom numbers.

As Jim said, the only purpose of an autonumber is to provide a unique identifier.  If you want something with meaning, you will have to generate your own key field.
0
 
LVL 12

Assisted Solution

by:funwithdotnet
funwithdotnet earned 250 total points
ID: 41832335
A primary key doesn't have to be 'auto-number'. It can be an integer that you set manually. You can get the greatest/next value with something like:
SELECT TOP (1) RecordId AS GreatestRecordId, (RecordId + 1) AS NextRecordId FROM MyTable ORDER BY RecordId DESC

Open in new window


You can insert the desired values via code like (pseudo-code):
myIntegerVariable = NextRecordId 
[ loop to insert rows...
INSERT INTO MyTable (ManualRecordId, Column1) VALUES (myIntegerVariable, thisRecordColumn1Value )
myIntegerVariable  += 1
]

Open in new window

However ... I agree with the other experts about using a uniqueidentifier (GUID). Especially for inserting related data in multiple tables via code. I even use GUIDs with temp data because it is so easy to relate data reliably.
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 41832421
No one recommended a GUID.  They cause other issues and so I would not recommend them unless you actually need them and those cases are extremely rare.  This isn't one of them.
0
 

Author Closing Comment

by:Tammy Allen
ID: 41837468
The creator of the database used auto numbers to identify individual line items in a budget.  The gap resulted from deleted records.   compiling cleaned up the database and accepting a gap due to deleted records will be acceptable.  Thank you team!
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

726 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