Solved

MS Access Duplicating records does not create a unique AutoNumber for ID column

Posted on 2013-05-17
3
427 Views
Last Modified: 2013-05-18
Hi. I am using the following SQL in an Access Query to duplicate rows for a certain TransactionID. It works fine but for some reason the new records have the same number
as the original records for the ID column, which is an AutoNumber column. I'm not sure how this is possible as I would expect unique numbers to be generated.


INSERT INTO Transactions
SELECT *
FROM Transactions
WHERE TransactionID=17330;
0
Comment
Question by:murbro
3 Comments
 
LVL 39

Accepted Solution

by:
als315 earned 250 total points
ID: 39176994
I think you allow duplicates in ID field. Can you upload sample DB with this table and  some dummy records. Try not to use *, add fields separately.
0
 
LVL 9

Assisted Solution

by:Armen Stein - Microsoft Access MVP since 2006
Armen Stein - Microsoft Access MVP since 2006 earned 250 total points
ID: 39177198
An append query like yours can set the value of an autonumber.  This is a handy capability if you are converting records from another system.  In your case, specify all the other fields but not the autonumber; you can't use *.

Cheers,
Armen Stein
0
 

Author Closing Comment

by:murbro
ID: 39178169
Thanks
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

744 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

14 Experts available now in Live!

Get 1:1 Help Now