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

Question

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?

score 2 · Accepted Answer · answered Sep 12 '20 at 00:27

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

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

1 Answers1