I am trying to use CREATE TABLE sql statements to make tables in Access. Its giving a sytax error. why?

Question

CREATE TABLE HISTORIAN(
HistorianID int NOT NULL,
Name char(30) NOT NULL,
Phone int NULL,
Email varchar(1000) NULL,
InstitutionID int NULL,
CONSTRAINT HistorianIDPK PRIMARY KEY (HistorianID),
CONSTRAINT NameAK1 UNIQUE (Name),
CONSTRAINT InstitutionIDFK FOREIGN KEY (InstitutionID) REFERENCES INSTITUTION (InstitutionID)
);

CREATE TABLE INSTITUTION(
InstitutionID int NOT NULL,
Name char(30) NOT NULL,
Website varchar(1000) NULL,
Phone int NULL,
Description varchar(1000) NULL,
AreaID int NULL,
CONSTRAINT InstitutionIDPK PRIMARY KEY (InstitutionID),
CONSTRAINT NameAK1 UNIQUE (Name),
CONSTRAINT AreaIDFK FOREIGN KEY (AreaID) REFERENCES AREA (AreaID)
);



CREATE TABLE AREA(
AreaID int NOT NULL,
Country varchar(1000) NOT NULL,
Region char(30) NOT NULL,
Latitude/Longitude char(1000) NULL,
InstitutionID int NULL,
Description varchar(1000) NULL,
CONSTRAINT AreaDPK PRIMARY KEY (AreaID),
CONSTRAINT RegionAK1 UNIQUE (Region),
CONSTRAINT InstitutionIDFK FOREIGN KEY (InstitutionID) REFERENCES INSTITUTION (InstitutionID)

Syntax error in CREATE TABLE statement;

Answer 1

Latitude/Longitude char(1000) NULL, // <<< error here

- you can't use fieldname with slash (/) in Latitude/Longitude
- your char(1000) is too long for all tables, change it to TEXT or MEMO field type.
- you must make REFERENCES for foreign key after you create all tables first.
in your tables, your HISTORIAN table make references to INSTITUTION, it means you can't create Historian Table before you create Institution Table. your INSTITUTION Table make references to AREA, and you can't create Institution Table before you create Area Table. but your AREA Table make references to HISTORIAN which you can't create.

Answer 2

To create table in access...
input fld details are different.

Ex...char/nvarchar/varch has to be interpreted as Text.
Int as number
more than 255 chars to be as Memo fld.
fld names ex Latitude/Longitude has to be like Latitude_Longitude