Solved

MSaccess Select identity

Posted on 2010-09-01
3
543 Views
Last Modified: 2012-05-10
Hello folks I've this statment and I need to retrieve the identity after I do an SELECT INTO. Im using UNION ALL

Thanks for any help
SELECT  IDENTITY(int, 1, 1) as aa, * 

INTO newTable

FROM (Table A

UNION ALL

TABLE B)

Open in new window

0
Comment
Question by:nkoriginal
3 Comments
 
LVL 32

Assisted Solution

by:Daniel Wilson
Daniel Wilson earned 250 total points
ID: 33577459
please try:

SELECT  @@IDENTITY as aa, * 
INTO newTable
FROM (Table A
UNION ALL
TABLE B)

Open in new window

0
 
LVL 5

Author Comment

by:nkoriginal
ID: 33577569
Thanks.!!
It's works but the column "aa" has a ZERO value.
How I can do to insert in that column (aa) an autoincrement value??

Thanks
0
 
LVL 77

Accepted Solution

by:
peter57r earned 250 total points
ID: 33577920
There isn't any way to specify an autoincrement field inan Access Make-table query.
You would have to run another quer/ execute another SQL string afterwards to Alter the table and column to set the autoincrement property.

ALTER TABLE table_name ALTER COLUMN column_name Counter

Alternativley use a pre-built table which includes an autonumber field, and use delete and append queries instead.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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.

759 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

18 Experts available now in Live!

Get 1:1 Help Now