| |
You are here: Experts > Computing/Technology > Business Software > Using MS Access > SQL Table Structure to Access
Using MS Access - SQL Table Structure to Access
Expert: Scottgem - 11/5/2009
Question
I am trying to assist a medical mon-profit that supports kids africa by building a database to maintain each patience data, medical histories, surgeries performed, etc.
Historically the group "Interplast" used a SQL database built by someone who knew someone. What was built in SQL was not very user friendly to the doctors asked to use it.
I got roped into assisting helping them build a MS Access database to accomplish the same need.
The designer of the original SQL database has not been very helpful. I do not have access to the SQL database to drive the data into a workable format. However the guy did provide me SQL file containing the SQL table structure detail.
I am not concerned about retaining any data from the SQL database. My goal is to avoid having to recreate the wheel. Ideally I would like to find a way of importing the SQL table schema into access.
I have attached an image of the table structure I have been provided.
Any assistance or direction would be greatly appriciated.
Below is a sample of the info from SQL.
CREATE TABLE basic_health_exams ("id" INTEGER PRIMARY KEY NOT NULL, "patient_id" integer DEFAULT NULL, "circumference_of_right_arm_in_cm" decimal DEFAULT NULL, "height_in_cm" float DEFAULT NULL, "circumference_of_left_arm_in_cm" decimal DEFAULT NULL, "weight_in_kg" float DEFAULT NULL);
CREATE TABLE clefting_histories ("id" INTEGER PRIMARY KEY NOT NULL, "patient_id" integer DEFAULT NULL, "side_of_hard_palate_cleft" varchar(255) DEFAULT NULL, "type_of_alveolus_cleft" varchar(255) DEFAULT NULL, "side_of_soft_palate_cleft" varchar(255) DEFAULT NULL, "type_of_hard_palate_cleft" varchar(255) DEFAULT NULL, "type_of_soft_palate_cleft" varchar(255) DEFAULT NULL, "parents_or_siblings_have_clefting" boolean DEFAULT NULL, "side_of_lip_cleft" varchar(255) DEFAULT NULL, "other_relatives_have_clefting" boolean DEFAULT NULL, "side_of_alveolus_cleft" varchar(255) DEFAULT NULL, "type_of_lip_cleft" varchar(255) DEFAULT NULL);
Thanks again,
Craig Jacobson
Answer
Frankly, from what you are showing me, I would not try to recreate this schema since its not normalized.
Lets take the first table, basic_health_exams for example. What that table looks like is that its using fields names to define data, whihc is wrong. Instead what that table should look like is this:
tblExams
Exams_ID (PK Autonumber)
Patient_ID (FK)
ExamTypeID (FK)
Measurement
MeasurementUnit
ExamTypeID would relate to a lookup table of exam types like this:
tluExamTypes
ExamTypeID (PK autonumber)
ExamType (values like Right arm circumference, height, weight, etc.)
The second table is a similar issue.
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
Add to this Answer Ask a Question
|
|
