?
Solved

Create Table ...long integer...

Posted on 2004-11-10
1
Medium Priority
?
871 Views
Last Modified: 2008-02-26
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)
;
0
Comment
Question by:SamJolly
1 Comment
 
LVL 16

Accepted Solution

by:
GreymanMSC earned 2000 total points
ID: 12542121
Q: Is it possible to specify 'Long Integer' in the Create SQL

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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses
Course of the Month13 days, 16 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question