Sample Access ANSI-92 SQL database creation script

sample-db.sql

alter table tblAddresses
drop constraint Addresses_NeedAtLeastOnePart;

alter table tblPeople
drop constraint People_AddressID;

alter table tblPeople
drop constraint People_NeedAtLeastOneName;

alter table tblPeople
drop constraint People_NeedAtLeastOneContactMethod;

alter table tblEmployees
drop constraint Employees_ID;

alter table tblEmployees
drop constraint Employees_UserGroup;

alter table tblProspects
drop constraint Prospects_ClientID;

alter table tblProspects
drop constraint Prospects_SalesRepID;

alter table tblProspects
drop constraint Prospects_InventoryID;

alter table tblProspects
drop constraint Prospects_InterestedIn;

alter table tblCustomers
drop constraint Customers_ID;

alter table tblResUnits
drop constraint ResUnits_AddressID;

alter table tblResUnits
drop constraint ResUnits_UnitType;

alter table tblInventory
drop constraint Inventory_ID;

alter table tblContracts
drop constraint Contracts_ProspectID;

alter table tblContracts
drop constraint Contracts_ResUnitID;

alter table tblContracts
drop constraint Contracts_CustomerID;

alter table tblContracts
drop constraint Contracts_TradeInID;

alter table tblOptionals
drop constraint Optionals_ContractID;

alter table tblEvents
drop constraint Events_ProspectID;

alter table tblEvents
drop constraint Events_Type;

drop table tblAddresses;
create table tblAddresses (
  ID autoincrement primary key
, Street varchar(200)
, City varchar(50)
, State char(2)
, Zip char(5)
, CommunityName varchar(50) unique
);

insert into tblAddresses (CommunityName)
values ('Fridley Terrace');

insert into tblAddresses (CommunityName)
values ('Gettysburg');

insert into tblAddresses (CommunityName)
values ('Lamplighter Village');

insert into tblAddresses (CommunityName)
values ('Village of Tampa');

insert into tblAddresses (CommunityName)
values ('Naples Estates');

insert into tblAddresses (CommunityName)
values ('Palm Beach Club');

insert into tblAddresses (CommunityName)
values ('Palm Breezes Club');

insert into tblAddresses (CommunityName)
values ('Island in the Sun');

drop table tblPeople;
create table tblPeople (
  ID autoincrement primary key
, FirstName varchar(50)
, LastName varchar(50)
, AddressID long constraint People_AddressID references tblAddresses (ID)
, AddressSuite varchar(20)
, HomePhone char(10)
, CellPhone char(10)
, Email varchar(100)
);

drop table tblEmployees;
create table tblEmployees (
  ID long primary key constraint Employees_ID references tblPeople (ID)
, EmployeeNumber long not null unique
, UserName varchar(20) not null unique
, UserGroup varchar(20) not null default SalesReps
);

drop table tblCustomers;
create table tblCustomers (
  ID long primary key constraint Customers_ID references tblPeople (ID)
, SSN char(10) not null
);

drop table tblResUnits;
create table tblResUnits (
  ID autoincrement primary key
, AddressID long constraint ResUnits_AddressID references tblAddresses (ID)
, UnitNumber long
, UnitType int not null
, Make varchar(100)
, Model varchar(100)
, SerialNum varchar(100)
, [Year] int
, Bedrooms int not null
, FloorLength float
, FloorWidth float
, HitchLength float
, HitchWidth float
, StockNum varchar(100)
, Color varchar(20)
, KeyNum varchar(100)
, CeilingRValue float
, CeilingThickness float
, CeilingTypeOfInsulation varchar(50)
, ExteriorRValue float
, ExteriorThickness float
, ExteriorTypeOfInsulation varchar(50)
, FloorsRValue float
, FloorsThickness float
, FloorsTypeOfInsulation varchar(50)
, BalanceDue currency not null default 0
);

drop table tblInventory;
create table tblInventory (
  ID long primary key constraint Inventory_ID references tblResUnits (ID)
, LedgerCost currency not null
, ApprovedRehab currency not null default 0
, Overrides currency not null default 0
, SalesAndCorpOverhead currency not null default 0
, ApprovedSalePrice currency not null
);

drop table tblProspects;
create table tblProspects (
  ID autoincrement primary key
, ClientID long not null constraint Prospects_ClientID references tblPeople (ID)
, SalesRepID long not null constraint Prospects_SalesRepID references tblEmployees (ID)
, InventoryID long not null constraint Prospects_InventoryID references tblInventory (ID)
, ReferralSource varchar(200)
, InterestedIn int not null default 1
, EstCloseDate date
, DepositAmount currency
, DepositPaid currency
, Closed yesno not null default false
);

drop table tblContracts;
create table tblContracts (
  ID autoincrement primary key
, ProspectID long not null constraint Contracts_ProspectID references tblProspects (ID)
, ResUnitID long not null constraint Contracts_ResUnitID references tblResUnits (ID)
, CustomerID long not null constraint Contracts_CustomerID references tblCustomers (ID)
, TradeInID long not null constraint Contracts_TradeInID references tblResUnits (ID)
, KelleyOrCapApproval yesno
, RegionalApproval yesno
, SalePrice currency not null
, SalesTax currency not null default 0
, SellerPayoffFirstLien currency not null default 0
, SellerPayoffSecondLien currency not null default 0
, BrokerFee currency not null default 0
, DocumentFee currency not null default 375
, DepositPaid currency not null default 0
, BuyerNewLoan currency
, TitleFee currency not null default 550
, StartDate date not null default date()
, EndDate date
, ProposedDelivery date not null
, FeesAndInsurance currency not null default 0
);

drop table tblOptionals;
create table tblOptionals (
  ID autoincrement primary key
, ContractID long not null constraint Optionals_ContractID references tblContracts (ID)
, Description varchar(100) not null
, Amount currency not null default 0
);

drop table tblEvents;
create table tblEvents (
  ID autoincrement primary key
, [Timestamp] datetime not null default now()
, ProspectID long not null constraint Events_ProspectID references tblProspects (ID)
, [Type] int not null
, Notes text
);

-- See below for code meanings;
alter table tblProspects
add constraint Prospects_InterestedIn check (
  InterestedIn in (1, 2, 3, 4)
);

alter table tblAddresses
add constraint Addresses_NeedAtLeastOnePart check (
   Street is not null
or City is not null
or State is not null
or Zip is not null
or CommunityName is not null
);

alter table tblPeople
add constraint People_NeedAtLeastOneName check (
   FirstName is not null
or LastName is not null
);

alter table tblPeople
add constraint People_NeedAtLeastOneContactMethod check (
   AddressID is not null
or (AddressID is not null and AddressSuite is not null)
or HomePhone is not null
or CellPhone is not null
or Email is not null
);

-- May add more user groups in future if necessary;
alter table tblEmployees
add constraint Employees_UserGroup check (
  UserGroup in (
    'SalesReps'
  )
);

-- 1 is new MH;
-- 2 is used MH;
-- 3 is apartment;
-- 4 is rental;
alter table tblResUnits
add constraint ResUnits_UnitType check (
  UnitType in (1, 2, 3, 4)
);

-- 1 is phone call;
-- 2 is visit;
-- 3 is email;
-- 4 is other;
alter table tblEvents
add constraint Events_Type check (
  [Type] in (1, 2, 3, 4)
);

drop view qryEmployees;
create view qryEmployees as 
select
  e.ID
, e.EmployeeNumber
, e.UserName
, e.UserGroup
, p.FirstName
, p.LastName
, p.AddressID
, p.AddressSuite
, p.HomePhone
, p.CellPhone
, p.Email
from tblEmployees as e
inner join tblPeople as p
on e.ID = p.ID;

drop procedure qryProspects;
create procedure qryProspects (
  CurrentUser varchar(20)
) as
select *
from tblProspects
where iif(
  CurrentUser = 'Admin'
, true
, SalesRepID = (
    select ID
    from tblEmployees
    where UserName = CurrentUser
  )
);

drop view qryCustomers;
create view qryCustomers as
select
  c.ID
, c.SSN
, p.FirstName
, p.LastName
, p.AddressID
, p.AddressSuite
, p.HomePhone
, p.CellPhone
, p.Email
from tblCustomers as c
inner join tblPeople as p
on c.ID = p.ID;

drop view qryInventory;
create view qryInventory as
select
  i.ID
, ru.AddressID
, ru.UnitNumber
, ru.UnitType
, ru.Make
, ru.Model
, ru.SerialNum
, ru.[Year]
, ru.Bedrooms
, ru.FloorLength
, ru.FloorWidth
, ru.HitchLength
, ru.HitchWidth
, ru.StockNum
, ru.Color
, ru.KeyNum
, ru.CeilingRValue
, ru.CeilingThickness
, ru.CeilingTypeOfInsulation
, ru.ExteriorRValue
, ru.ExteriorThickness
, ru.ExteriorTypeOfInsulation
, ru.FloorsRValue
, ru.FloorsThickness
, ru.FloorsTypeOfInsulation
, ru.BalanceDue
, i.LedgerCost
, i.ApprovedRehab
, i.Overrides
, i.SalesAndCorpOverhead
, i.ApprovedSalePrice
from tblResUnits as ru
inner join tblInventory as i
on ru.ID = i.ID;

drop user
  im1
, sr1;
drop group
  InventoryManagers
, SalesReps;

create group InventoryManagers 1234;
create group SalesReps 5678;
create user im1 'im1' 9012;
create user sr1 'sr1' 3456;
add user im1 to InventoryManagers;
add user sr1 to SalesReps;

grant
  select
, delete
, insert
, update
, drop
, create
on table MSysAccessStorage
to
  InventoryManagers
, SalesReps;

grant
  select
, delete
, insert
, update
, drop
, create
on table MSysObjects
to
  InventoryManagers
, SalesReps;

grant
  select
, delete
, insert
, update
, drop
, create
on table tblProspects
to SalesReps