AutoNumber in ACCESS

[Leon1977]

I need to write a Create table SQL script for MS ACCESS
but I need a autonumber field and a double (fixed 2)
How should I define theese datatypes

Create table table_name(
id int primary key /autonumber/,
price float /fixed(2)/
)

something like this

 

[SQLSister]

Autonumber set to integer with the identity property, use decimal for the other

Create table table_name(
id [int] IDENTITY (1, 1) NOT NULL,
price [decimal](10, 2) NULL)
)

Actually the number of places allowed in the decimal can be adjusted based on how big you think the number will get. This one contains 10 places, 2 of which are to the right of the decimal point. The automunber will start with one and increment by one each time.

By the way ID is not a really good name for a field. Use PriceID or something similar. Later on (when you have 50 tables) you will want to know which table this ID relates to, so it is better to start with a unique name now.

 

[Leon1977]

You seems not to understand my question.
I need this to work with MS Access
I have copied this code in the SQL VIEW of the Acces query
and a syntax error occured
As far as I know identity works with MS SQL Server

 

[r937]

for access, use COUNTER, not IDENTITY


rudy

 

[SQLSister]

This is the MS SQL Server forum, please refer access questions to the Access forum.