Create Table ...long integer...

Hi everybody,

I am trying to create a new table which contains a long integer as the data type for a foreign key. The following SQL was created by  ER tool

CREATE TABLE Address (
Id Counter NOT NULL,
Building Text(100),
Street Text(100),
TownCity Text(100),
CountyArea Text(100),
Country Text(50),
PostCode Text(50),
TelNo Text(50),
FaxNo Text(50),
Type Text(10),
FkCompanyId long integer
)
;

But it does not run - 'Long integer' needs changing to 'number', however then the field size propery gets set to 'double' by default. This then causes all my FK creation statements to fall over as FKs, when matched against autonumber primary key field must be 'Long integer;

ALTER TABLE Address ADD CONSTRAINT FK_Address_Company
FOREIGN KEY (FkCompanyId) REFERENCES Company (Id);

Is it possible to specify 'Long Integer' in the Create SQL.

Thanks,

Sam

p.s Is the comment character in Access SQL "/* */" and "//". Can this be used in the Access Query builder?

p.p.s I must admit that I have become a little frustrated with the different Query builder modes in Access , and have instead opted to evaluate a more general and powerful DB utility called urSQL which enables one to run SQL via ODBC against a number of Databases including MSAccess. It will also let me run batch DDL, and select parts to run specifically. Any similar experiences appreciated, and perhaps other tool recommendations?

See below for complete ER tool generated DDL:

DROP TABLE Address
;

DROP TABLE Company
;

DROP TABLE DeliveryUnit
;

DROP TABLE History
;

DROP TABLE OrderHeader
;

DROP TABLE Person
;

DROP TABLE Product
;

DROP TABLE Requisition
;

DROP TABLE RequisitionItem
;

DROP TABLE Supplier
;

DROP TABLE Type
;


CREATE TABLE Address (
      Id Counter NOT NULL,
      Building Text(100),
      Street Text(100),
      TownCity Text(100),
      CountyArea Text(100),
      Country Text(50),
      PostCode Text(50),
      TelNo Text(50),
      FaxNo Text(50),
      Type Text(10),
      FkCompanyId Long Integer
)
;

CREATE TABLE Company (
      Id Counter NOT NULL,
      Name Text(100),
      IsSupplier Text(5),
      IsCustomer Text(5),
      Type Text(50)
)
;

CREATE TABLE DeliveryUnit (
      Id Counter NOT NULL,
      Name Text(50),
      FkOrderId Long Integer,
      DeliveryInstruction Text(200)
)
;

CREATE TABLE History (
      Id Counter NOT NULL,
      Description Text(50),
      EventDate DateTime,
      ByWhom Text(50),
      FkOrderId Long Integer
)
;

CREATE TABLE OrderHeader (
      Id Counter NOT NULL,
      FkGoodsCompanyId Long Integer,
      Status Text(50),
      Type Text(50),
      FkTransportCompanyId Long Integer,
      GoodsCompanyInstructions Text(50),
      TransportCompanyInstructions Text(200)
)
;

CREATE TABLE Person (
      Id Counter NOT NULL,
      Name Text(50),
      TelNo Text(50),
      FaxNo Text(50),
      FkCompanyId Long Integer,
      Email Text(50)
)
;

CREATE TABLE Product (
      Id Counter NOT NULL
)
;

CREATE TABLE Requisition (
      Id Counter NOT NULL,
      FkCompanyId Long Integer,
      Instructions Text(200),
      RaisedBy Text(50),
      Owner Text(50),
      FKClientContactId Long Integer,
      CustomerOrderNo Text(50)
)
;

CREATE TABLE RequisitionItem (
      Id Counter NOT NULL,
      FkOrderId Long Integer,
      FkRequisitionId Long Integer,
      Name Text(100),
      Description Text(200),
      Quantity Double,
      PricePerUnit Double,
      Instructions Text(200),
      FkDeliveryUnitId Long Integer,
      Commission Double
)
;

CREATE TABLE Supplier (
      Id Counter NOT NULL
)
;

CREATE TABLE Type (
      Id Counter NOT NULL,
      Name Text(50),
      Description Text(200),
      Category Text(50)
)
;


ALTER TABLE Address ADD CONSTRAINT PK_Address
PRIMARY KEY (Id)
;

ALTER TABLE Company ADD CONSTRAINT PK_Customer
PRIMARY KEY (Id)
;

ALTER TABLE DeliveryUnit ADD CONSTRAINT PK_DeliveryUnit
PRIMARY KEY (Id)
;

ALTER TABLE History ADD CONSTRAINT PK_History
PRIMARY KEY (Id)
;

ALTER TABLE OrderHeader ADD CONSTRAINT PK_Order
PRIMARY KEY (Id)
;

ALTER TABLE Person ADD CONSTRAINT PK_Person
PRIMARY KEY (Id)
;

ALTER TABLE Product ADD CONSTRAINT PK_Product
PRIMARY KEY (Id)
;

ALTER TABLE Requisition ADD CONSTRAINT PK_Requisition
PRIMARY KEY (Id)
;

ALTER TABLE RequisitionItem ADD CONSTRAINT PK_RequisitionItem
PRIMARY KEY (Id)
;

ALTER TABLE Supplier ADD CONSTRAINT PK_Supplier
PRIMARY KEY (Id)
;

ALTER TABLE Type ADD CONSTRAINT PK_Type
PRIMARY KEY (Id)
;




ALTER TABLE Address ADD CONSTRAINT FK_Address_Company
FOREIGN KEY (FkCompanyId) REFERENCES Company (Id)
;

ALTER TABLE DeliveryUnit ADD CONSTRAINT FK_DeliveryUnit_Order
FOREIGN KEY (FkOrderId) REFERENCES OrderHeader (Id)
;

ALTER TABLE History ADD CONSTRAINT FK_History_Order
FOREIGN KEY (FkOrderId) REFERENCES OrderHeader (Id)
;

ALTER TABLE OrderHeader ADD CONSTRAINT FK_Order_GoodsCompany
FOREIGN KEY (FkGoodsCompanyId) REFERENCES Company (Id)
;

ALTER TABLE OrderHeader ADD CONSTRAINT FK_OrderHeader_Company
FOREIGN KEY (FkTransportCompanyId) REFERENCES Company (Id)
;

ALTER TABLE Person ADD CONSTRAINT FK_Person_Company
FOREIGN KEY (FkCompanyId) REFERENCES Company (Id)
;

ALTER TABLE Requisition ADD CONSTRAINT FK_Requisition_Company
FOREIGN KEY (FkCompanyId) REFERENCES Company (Id)
;

ALTER TABLE Requisition ADD CONSTRAINT FK_Requisition_Person
FOREIGN KEY (FKClientContactId) REFERENCES Person (Id)
;

ALTER TABLE RequisitionItem ADD CONSTRAINT FK_RequisitionItem_DeliveryUnit
FOREIGN KEY (FkDeliveryUnitId) REFERENCES DeliveryUnit (Id)
;

ALTER TABLE RequisitionItem ADD CONSTRAINT FK_RequisitionItem_Order
FOREIGN KEY (FkOrderId) REFERENCES OrderHeader (Id)
;

ALTER TABLE RequisitionItem ADD CONSTRAINT FK_RequisitionItem_Requisition
FOREIGN KEY (FkRequisitionId) REFERENCES Requisition (Id)
;
SamJollyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GreymanMSCCommented:
Q: Is it possible to specify 'Long Integer' in the Create SQL

A: Yes.  The type is just called Long.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.