sqlms-accessselect-into

MSACCESS - How to add a primary key using a query make table

I have a query make table with this SQL:

SELECT DISTINCT tblStore.Position, tblStore.Branch, tblStore.Location, tblStore.Operator INTO tblAllPositions
FROM tblStore
WHERE (((tblStore.Position) Is Not Null))
ORDER BY tblStore.Position, tblStore.Branch, tblStore.Location;

I want to add to the new table tblAllPositions a field called ID_Positions and set it as primary key autonumber.

How can i do that?

Solution

  • Consider:

    ALTER TABLE tblAllPositions 
ADD COLUMN ID_Positions COUNTER, 
CONSTRAINT PrimaryKey PRIMARY KEY (ID_Positions)

    I constructed that SQL after review of ALTER TABLE and CREATE TABLE tutorials at https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/data-definition-language