Search code examples
sqlms-accesscreate-table

Creating a Table but getting Syntax error in field definition

Create Table Order_Line
(
    Order_Num Char(5),
    Item_Num Char(4),
    Num_Ordered Decimal(3,0),
    Quoted_Price Decimal(6,2),
    Primary Key (Order_Num, Item_Num)
);

I'm taking a database class and my assignment Is asking to create a table for Order Line. When I select run to create the table I'm getting a "syntax error" in field definition and it's highlighting DECIMAL. I'm writing this query in Microsoft Access. Once again I'm not even classified as a rookie on this and would greatly appreciate someone helping explain what I'm doing wrong with this. Thanks in advance Stack overflow community.

Solution

  • You can use Currency to get you over this detail.

    Create Table Order_Line
(
Order_Num Char(5),
Item_Num Char(4),
Num_Ordered Currency,
Quoted_Price Currency,
Primary Key (Order_Num, Item_Num)
);

    Currency is like Decimal (15,4)

    I think your original sql statement would have worked in previous versions of Access. The Manual Query Designer will still allow you to change the decimal places per field for Number and Large Number.