0

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?

Andre
  • 26,751
  • 7
  • 36
  • 80
Pankaj
  • 9,749
  • 32
  • 139
  • 283

2 Answers2

0

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.

Andre
  • 26,751
  • 7
  • 36
  • 80
  • how do i declare precision and scale with double? – Pankaj Oct 17 '22 at 15:12
  • As I wrote, you can't. `Double has no parameters for precision or scale. It's a fixed 8-byte floating point format.` – Andre Oct 17 '22 at 15:12
  • then how do I mention correct scale and precision with script? may be decimal? – Pankaj Oct 17 '22 at 15:34
  • 1
    I feel we are running in circles. :) If you want defined scale and precision, then yes: use Decimal. There are several ways to script it: https://stackoverflow.com/questions/180929/how-do-i-create-a-decimal-field-in-access-with-alter-table @Pankaj – Andre Oct 17 '22 at 18:57
-1

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)
)
Awais ali
  • 109
  • 6
  • I am using 2007, it is not accepting `auto_increment`. instead `autoincrement` works fine. `Decimal(12, 2)` not working in 2007 – Pankaj Oct 15 '22 at 08:25
  • You can use the design view to create the table. If you insist to use query. Use number data type, and it will work fine. – Awais ali Oct 15 '22 at 08:31