[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
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
Medium Priority
?
73 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 39

Accepted Solution

by:
PatHartman earned 1000 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 1000 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 39

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

650 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