?
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
?
68 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 38

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 38

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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

752 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