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
48 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
5 Comments
 
LVL 65

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 34

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 34

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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.

864 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

19 Experts available now in Live!

Get 1:1 Help Now