Create Table query is giving syntax error due to decimal format-CodePudding

I am using Access 2007. There is following query to create the table.

I am trying to set max length as 12 and 2 for decimal precision for quantity column

create table tbl_sales
(
     sales_id autoincrement primary key,
     item_id number,
     quantity_sold double(12, 2)
)

but due to some reasons it gives syntax error message at double. Am I missing anything?

CodePudding user response：

Double has no parameters for precision or scale. It's a fixed 8-byte floating point format.

I still like this overview of data types best:
http://allenbrowne.com/ser-49.html

For DECIMAL (precision, scale) it states:

Not available in the Access query interface or DAO. Use ADO to Execute the DDL query statement.

So you will have to do exactly this if you want to run a CREATE TABLE statement with Decimal type.

Alternatively, use DAO with the Database.CreateTableDef method.

If you want to use Double, it's just that.
This works for me in Access 2010, executed from query design:

create table tbl_sales
(
     sales_id autoincrement primary key,
     item_id long,
     quantity_sold double
)

Note that number also creates a Double column, so if you want Long Int for an ID column, use Long.

CodePudding user response：

There are two major problems in the quer. First The auto increment is not correct. Second there should be decimal in place of double. here is the query .

   create table tbl_sales
(
     sales_id INT AUTO_INCREMENT primary key,
     item_id number,
     quantity_sold Decimal(12, 2)
)