Microsoft Access Script to Create Tables

We do not recommend Access.

It has limitations and problems that make it unacceptable for business solutions. If price is an issue, we recommend the free MySQL software. This is a free, industrial strength Open Source database available at www.MySQL.com

CREATE TABLE edi_claims (

ID INT NOT NULL AUTO_INCREMENT KEY,

ClaimNo varchar (50) NOT NULL ,

HIPAAsuiteID varchar (9) NULL ,

TradingPartnerIDType char (2) NULL ,

TradingPartnerID varchar (15) NOT NULL ,

SubmitterName varchar (35) NULL ,

SubmitterID varchar (30) NULL ,

SubmitterContact varchar (60) NULL ,

SubmitterTel varchar (20) NULL ,

SubmitterTelExt varchar (10) NULL ,

SubmitterFax varchar (20) NULL ,

SubmitterEmail varchar (80) NULL ,

ReceiverName varchar (35) NULL ,

BillProvIDType char (2) NULL ,

BillProvID varchar (80) NULL ,

BillProvLast varchar (35) NULL ,

BillProvFirst varchar (25) NULL ,

BillProvMiddle varchar (25) NULL ,

BillProvSuffix varchar (10) NULL ,

BillProvSpecialty varchar (10) NULL ,

BillProvAddress varchar (55) NULL ,

BillProvAddress2 varchar (55) NULL ,

BillProvCity varchar (30) NULL ,

BillProvState char (2) NULL ,

BillProvZip varchar (15) NULL ,

BillProvContact varchar (60) NULL ,

BillProvTel varchar (80) NULL ,

BillProvTelExt varchar (80) NULL ,

BillProvFax varchar (80) NULL ,

BillProvEmail varchar (80) NULL ,

PayToProvIDType char (2) NULL ,

PaytoProvID varchar (80) NULL ,

PaytoProvLast varchar (35) NULL ,

PaytoProvFirst varchar (25) NULL ,

PayToProvMiddle varchar (25) NULL ,

PayToProvSuffix varchar (10) NULL ,

PayToProvSpecialty varchar (10) NULL ,

PaytoProvAddress varchar (55) NULL ,

PayToProvAddress2 varchar (55) NULL ,

PaytoProvCity varchar (30) NULL ,

PaytoProvState char (2) NULL ,

PaytoProvZip varchar (15) NULL ,

RendProvIDType char (2) NULL ,

RendProvID varchar (80) NULL ,

RendProvLast varchar (35) NULL ,

RendProvFirst varchar (25) NULL ,

RendProvMiddle varchar (25) NULL ,

RendProvSuffix varchar (10) NULL ,

RendProvSpecialty varchar (10) NULL ,

AttendProvLast varchar (35) NULL ,

AttendProvFirst varchar (25) NULL ,

AttendProvMiddle varchar (25) NULL ,

AttendProvSuffix varchar (10) NULL ,

AttendProvIDType char (2) NULL ,

AttendProvID varchar (80) NULL ,

AttendProvSpecialty varchar (10) NULL ,

OperatingProvLast varchar (35) NULL ,

OperatingProvFirst varchar (25) NULL ,

OperatingProvMiddle varchar (25) NULL ,

OperatingProvSuffix varchar (10) NULL ,

OperatingProvIDType char (2) NULL ,

OperatingProvID varchar (80) NULL ,

OperatingProvSpecialty varchar (10) NULL ,

OtherProvLast varchar (35) NULL ,

OtherProvFirst varchar (25) NULL ,

OtherProvMiddle varchar (25) NULL ,

OtherProvSuffix varchar (10) NULL ,

OtherProvIDType varchar (100) NULL ,

OtherProvID varchar (100) NULL ,

OtherProvSpecialty varchar (100) NULL ,

FacilityType char (2) NULL ,

FacilityIDType char (2) NULL ,

FacilityID varchar (80) NULL ,

FacilityName varchar (80) NULL ,

FacilityAddress varchar (55) NULL ,

FacilityAddress2 varchar (55) NULL ,

FacilityCity varchar (30) NULL ,

FacilitySpecialty varchar (100) NULL ,

FacilityState char (2) NULL ,

FacilityZip varchar (15) NULL ,

OutsideLab char (1) NULL ,

LabCharge varchar (10) NULL ,

SubscriberLast varchar (35) NULL ,

SubscriberFirst varchar (25) NULL ,

SubscriberMiddle varchar (25) NULL ,

SubscriberSuffix varchar (10) NULL ,

SubscriberAddress varchar (55) NULL ,

SubscriberAddress2 varchar (55) NULL ,

SubscriberCity varchar (30) NULL ,

SubscriberState char (2) NULL ,

SubscriberZip varchar (15) NULL ,

SubscriberIDType char (2) NULL ,

SubscriberID varchar (80) NULL ,

SubscriberDOB varchar (10) NULL ,

SubscriberSex char (1) NULL ,

InsuranceType char (2) NULL ,

GroupNo varchar (50) NULL ,

GroupName varchar (60) NULL ,

FilingIndicator char (2) NULL ,

PatientLast varchar (35) NULL ,

PatientFirst varchar (25) NULL ,

PatientMiddle varchar (25) NULL ,

PatientSuffix varchar (10) NULL ,

PatientAddress varchar (55) NULL ,

PatientAddress2 varchar (55) NULL ,

PatientCity varchar (30) NULL ,

PatientState char (2) NULL ,

PatientZip varchar (15) NULL ,

PatientIDType char (2) NULL ,

PatientID varchar (80) NULL ,

PatientDOB varchar (50) NULL ,

PatientSex char (1) NULL ,

PatientRelationship char (2) NULL ,

PatientPaid varchar (10) NULL ,

PregnancyIndicator char (1) NULL ,

OtherInsuredLast varchar (35) NULL ,

OtherInsuredFirst varchar (25) NULL ,

OtherInsuredMiddle varchar (25) NULL ,

OtherInsuredSuffix varchar (10) NULL ,

OtherInsuredPolicy varchar (50) NULL ,

OtherInsuredDOB varchar (10) NULL ,

OtherInsuredSex char (1) NULL ,

OtherInsuredRelationship char (2) NULL ,

OtherPayerName varchar (35) NULL ,

OtherPayerID varchar (80) NULL ,

OtherPayerPaid varchar (10) NULL ,

OtherPayerGroupName varchar (100) NULL ,

OtherPayerGroupNo varchar (100) NULL ,

PayerName varchar (35) NULL ,

PayerIDType char (2) NULL ,

PayerID varchar (80) NULL ,

PayerAddress varchar (55) NULL ,

PayerAddress2 varchar (55) NULL ,

PayerCity varchar (30) NULL ,

PayerState char (2) NULL ,

PayerZip varchar (15) NULL ,

Amount varchar (50) NULL ,

EstimatedAmountDue varchar (10) NULL ,

PlaceOfService char (2) NULL ,

SubmitReason char (1) NULL ,

ProviderSignature char (1) NULL ,

ProviderAcceptsAssignment char (1) NULL ,

BenefitAssignment char (1) NULL ,

InfoReleaseCode char (1) NULL ,

PatientSignatureCode char (1) NULL ,

RelatedCauses char (2) NULL ,

RelatedCausesState char (2) NULL ,

SpecialProgramCode char (3) NULL ,

ProviderParticipation char (1) NULL ,

ServiceDateFrom varchar (15) NULL ,

ServiceDateTo varchar (15) NULL ,

OnsetDate varchar (15) NULL ,

SimilarSymptomsDate varchar (10) NULL ,

DisabilityBegin varchar (10) NULL ,

DisabilityEnd varchar (10) NULL ,

HospitalizationBegin varchar (20) NULL ,

HospitalizationEnd varchar (10) NULL ,

AccidentDate varchar (20) NULL ,

AdmissionDate varchar (15) NULL ,

AdmissionHour varchar (10) NULL ,

AdmissionType char (1) NULL ,

AdmissionSource char (1) NULL ,

DischargeHour varchar (10) NULL ,

PatientStatus char (2) NULL ,

MedicalRecordNumber varchar (50) NULL ,

CoveredDays varchar (4) NULL ,

NonCoveredDays varchar (4) NULL ,

COBDays varchar (4) NULL ,

LifeTimeReserveDays varchar (4) NULL ,

PriorAuthorization varchar (50) NULL ,

OrigRefNo varchar (50) NULL ,

RefProvLast varchar (35) NULL ,

RefProvFirst varchar (25) NULL ,

RefProvMiddle varchar (25) NULL ,

RefProvSuffix varchar (10) NULL ,

RefProvIDType char (2) NULL ,

RefProvID varchar (80) NULL ,

RefProvSpecialty varchar (10) NULL ,

AdmitDiagnosis varchar (10) NULL ,

ECode varchar (100) NULL ,

PrincipalDiagnosis varchar (10) NULL ,

Diag2 varchar (10) NULL ,

Diag3 varchar (10) NULL ,

Diag4 varchar (10) NULL ,

Diag5 varchar (10) NULL ,

Diag6 varchar (10) NULL ,

Diag7 varchar (10) NULL ,

Diag8 varchar (10) NULL ,

Diag9 varchar (10) NULL ,

PrincipalProcedure varchar (10) NULL ,

PrincipalProcedureDate varchar (10) NULL ,

Proc2 varchar (50) NULL ,

Proc2Date varchar (10) NULL ,

Proc3 varchar (10) NULL ,

Proc3Date varchar (10) NULL ,

Proc4 varchar (10) NULL ,

Proc4Date varchar (10) NULL ,

Proc5 varchar (10) NULL ,

Proc5Date varchar (10) NULL ,

Proc6 varchar (10) NULL ,

Proc6Date varchar (10) NULL ,

DRG varchar (15) NULL ,

ValueCode1 char (3) NULL ,

ValueAmount1 varchar (10) NULL ,

ValueCode2 char (3) NULL ,

ValueAmount2 varchar (10) NULL ,

ValueCode3 char (3) NULL ,

ValueAmount3 varchar (10) NULL ,

ValueCode4 char (3) NULL ,

ValueAmount4 varchar (10) NULL ,

ValueCode5 char (3) NULL ,

ValueAmount5 varchar (10) NULL ,

ValueCode6 char (3) NULL ,

ValueAmount6 varchar (10) NULL ,

ValueCode7 varchar (10) NULL ,

ValueAmount7 varchar (10) NULL ,

ValueCode8 char (3) NULL ,

ValueAmount8 varchar (10) NULL ,

ValueCode9 char (3) NULL ,

ValueAmount9 varchar (10) NULL ,

ValueCode10 char (3) NULL ,

ValueAmount10 varchar (10) NULL ,

ValueCode11 char (3) NULL ,

ValueAmount11 varchar (10) NULL ,

ValueCode12 char (3) NULL ,

ValueAmount12 varchar (10) NULL ,

ConditionCode1 varchar (30) NULL ,

ConditionCode2 varchar (30) NULL ,

ConditionCode3 varchar (30) NULL ,

ConditionCode4 varchar (30) NULL ,

ConditionCode5 varchar (30) NULL ,

ConditionCode6 varchar (30) NULL ,

ConditionCode7 varchar (30) NULL ,

ConditionCode8 varchar (30) NULL ,

FileName varchar (100) NULL ,

OccurranceCode1 char (3) NULL ,

OccurranceDate1 varchar (10) NULL ,

OccurranceCode2 char (3) NULL ,

OccurranceDate2 varchar (10) NULL ,

OccurranceCode3 char (3) NULL ,

OccurranceDate3 varchar (10) NULL ,

OccurranceCode4 char (3) NULL ,

OccurranceDate4 varchar (10) NULL ,

OccurranceCode5 char (3) NULL ,

OccurranceDate5 varchar (10) NULL ,

OccurranceCode6 char (3) NULL ,

OccurranceDate6 varchar (10) NULL ,

OccurranceCode7 char (3) NULL ,

OccurranceDate7 varchar (10) NULL ,

OccurranceCode8 char (3) NULL ,

OccurranceDate8 varchar (10) NULL ,

OccurranceSpanCode1 char (3) NULL ,

OccurranceSpanFrom1 varchar (10) NULL ,

OccurranceSpanTo1 varchar (10) NULL ,

OccurranceSpanCode2 char (3) NULL ,

OccurranceSpanFrom2 varchar (10) NULL ,

OccurranceSpanTo2 varchar (10) NULL ,

Test_Prod char (1) NULL ,

ClaimType char (1) NULL ,

TypeofBill varchar (20) NULL ,

Remark1 varchar (90) NULL ,

Remark2 varchar (90) NULL ,

Remark3 varchar (90) NULL ,

Remark4 varchar (90) NULL ,

Claim_status char (1) NULL ,

TargetTP varchar (30) NULL ,

ClearingHouseID varchar (100) NULL,

RepricedClaimNumber varchar (100) NULL,

RepricingMethodology varchar (3) NULL,

RepricedAmount varchar (15) NULL,

SavingsAmount varchar (15) NULL,

RepricerID varchar (30) NULL,

RejectReason varchar (3) NULL

) ;

CREATE TABLE edi_claimdetail (

ID INT NOT NULL AUTO_INCREMENT KEY,

ClaimID varchar (50) NOT NULL ,

HIPAAsuiteID varchar (10) NULL ,

LineNumber decimal(18, 0) NOT NULL ,

LineID varchar (50) NULL ,

ServiceDateFrom varchar (50) NULL ,

ServiceDateTo varchar (50) NULL ,

FacilityCode varchar (50) NULL ,

RevenueCode varchar (50) NULL ,

ProcedureCode varchar (50) NULL ,

Description varchar (100) NULL,

Amount varchar (50) NULL ,

Unit varchar (50) NULL ,

Quantity varchar (50) NULL ,

UnitRate varchar (50) NULL ,

NonCovered varchar (50) NULL ,

Paid varchar (50) NULL ,

PlaceOfService char (3) NULL ,

Modifier1 char (3) NULL ,

Modifier2 char (3) NULL ,

Modifier3 char (3) NULL ,

Modifier4 char (3) NULL ,

DiagPointer1 char (1) NULL ,

DiagPointer2 char (1) NULL ,

DiagPointer3 char (1) NULL ,

DiagPointer4 char (1) NULL ,

ToothNumber char (2) NULL ,

Surface varchar (10) NULL ,

EmergencyIndicator char (1) NULL,

RepricingMethodology varchar (3) NULL,

RepricedAmount varchar (15) NULL,

SavingsAmount varchar (15) NULL,

RepricerID varchar (30) NULL,

RejectReason varchar (3) NULL

) ;