Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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
?
77 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 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 40

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 40

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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Viewers will learn how the fundamental information of how to create a table.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

571 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