CREATE TABLE
Used
to
create the tables where data will be stored.
=========
Example:
1. Open the Orders table in design view.
2. Data type specified for each field (column)
Autonumber (Other
databases: identity)
Text
(char(n),
varchar(n))
Number
Date/Time
Currency
(money)
Additional data types not
used in this
table:
Memo
(long (Oracle, up to 2 GB text)
Yes/No
(boolean)
OLE Object (blob, clob, raw)
Some additional data types in
other
database systems:
Timestamp
Interval
Enum
Set
3. Select each field and notice its properties
in the Field Properties grid.
To view table structure and
column
properties in other databases:
describe
tablename
and
select [properties] from [system tables]
4. Close the Orders table.
Exercise:
Create a table to store personnel data, with a StaffID column as
primary
key
1. Type this SQL statement in the SQL query
design window:
CREATE TABLE Personnel (
StaffID text(9)
CONSTRAINT StaffPK PRIMARY KEY,
LastName text(15)
not null,
FirstName text(15)
not null,
Birthday date,
Department text(12)
null);
2. Execute the statement. If
Access reports syntax errors, find and
correct them.
3. Save the query as DefinePersonnel and close
it.
4. Run a query to select all records from the
new table:
SELECT * FROM Personnel;
The query returns one blank
record (in
other databases: 0 rows).
Close the query.
4. Open the new table in datasheet view – it is
empty and ready for data entry.
5. Change to design view and compare with the
SQL statement.
6. Choose View / Indexes and compare with the
constraint created on StaffID.