Hi all!
I am relatively new to database design so I tried to be as descriptive as possible. I designed a data extraction from medical record systems to collect the data for a chart review project and the raw data is stored in excel.
I have five separate excel spread sheets, a patient list, a patient visit data sheet with each individual's visits to the clinic recorded as one record (row), a medication list with each patient having an individual record per medication, a problem list structured similar to the medication list, and then finally a lab test and result, again similar.
My Problem: I'm stuck importing the visit data’s vital results into a table with append queries.
A) My Process thus far (See attachment: Relationship.png)
1) I have used the original excel Patient PER visit data sheet and imported it into access as "RawPatientVisitData." This is my raw data table.
2) I used an append query to build a PatientList table that contains only distinct patients from the RawPatientVisitData. I selected only distinct rows from the raw data and left any data regarding to the visit itself alone. The patientlist table is just data that won't change with visits and thus excluded data collected during the visit itself (Vitals, or tests for example).
3) Since each patient can also have multiple visits, I have built a PatientVisit table. I included the SQL (I didn't use it to build - I used design view).
INSERT INTO PatientVisits ( PatientID, VisitDate, StaffID )
SELECT PatientList.PatientID, RawPatientVisitData.VisitDate, RawPatientVisitData.StaffID
FROM RawPatientVisitData INNER JOIN PatientList ON RawPatientVisitData.PID = PatientList.PID;
The PatientVisit table includes the PatientVisitID as the primary key so that EACH visit and patient combination has an autonumbered uniqueID. This was generated as an autonumber when the data was appended with 1 being the oldest visit date and 2507 as the most recent visit date.
4) Since each patient has multiple visits and each visit has multiple vital results (BPSys, BPDiastolic, Height, Weight and BMI) recorded, I now want to append the vitals results that are recorded in the RawPatientVisitData to a table called VisitVitals.
5) I have already made a table called Vitals with a vitalID primary key associated with each type of vital.
B) Here is my problem.
I’ve used pictures to demonstrate most if it.
When I try to do the following query: (SEE Query View.png)
Here is the SQL:
SELECT RawPatientVisitData.BPSys, PatientVisits.PatientVisitID, 1 AS Expr1
FROM RawPatientVisitData INNER JOIN (PatientList INNER JOIN PatientVisits ON PatientList.PatientID = PatientVisits.PatientID) ON RawPatientVisitData.PID = PatientList.PID;
This query results in the left part of this picture: (See Query View vs Patient Visits)
The problem is that PatientVisitID’s are repeated seemingly according to the number of times a patient had seen the clinic? But if I look at my PatientVisit table, it shows that each PatientVisitID is still unique to each patient and visit combo (See right side of picture)
I still appended the data to the VisitVitals table. When I did this, I got all of the vital results of a patient listed into the patient visitID that should be unique for one visit date.
See: Query Question.png
and then the next date also shows:
See: Query Question2.png
Why is it when I look under the patientlist subfields for the visits I see multiple PatientVisitID and test results being repeated on dates that they didn't occur on. Shouldn't each patientVisitID show only the tests that occured on that day?
I am relatively new to database design so I tried to be as descriptive as possible. I designed a data extraction from medical record systems to collect the data for a chart review project and the raw data is stored in excel.
I have five separate excel spread sheets, a patient list, a patient visit data sheet with each individual's visits to the clinic recorded as one record (row), a medication list with each patient having an individual record per medication, a problem list structured similar to the medication list, and then finally a lab test and result, again similar.
My Problem: I'm stuck importing the visit data’s vital results into a table with append queries.
A) My Process thus far (See attachment: Relationship.png)
1) I have used the original excel Patient PER visit data sheet and imported it into access as "RawPatientVisitData." This is my raw data table.
2) I used an append query to build a PatientList table that contains only distinct patients from the RawPatientVisitData. I selected only distinct rows from the raw data and left any data regarding to the visit itself alone. The patientlist table is just data that won't change with visits and thus excluded data collected during the visit itself (Vitals, or tests for example).
3) Since each patient can also have multiple visits, I have built a PatientVisit table. I included the SQL (I didn't use it to build - I used design view).
INSERT INTO PatientVisits ( PatientID, VisitDate, StaffID )
SELECT PatientList.PatientID, RawPatientVisitData.VisitDate, RawPatientVisitData.StaffID
FROM RawPatientVisitData INNER JOIN PatientList ON RawPatientVisitData.PID = PatientList.PID;
The PatientVisit table includes the PatientVisitID as the primary key so that EACH visit and patient combination has an autonumbered uniqueID. This was generated as an autonumber when the data was appended with 1 being the oldest visit date and 2507 as the most recent visit date.
4) Since each patient has multiple visits and each visit has multiple vital results (BPSys, BPDiastolic, Height, Weight and BMI) recorded, I now want to append the vitals results that are recorded in the RawPatientVisitData to a table called VisitVitals.
5) I have already made a table called Vitals with a vitalID primary key associated with each type of vital.
B) Here is my problem.
I’ve used pictures to demonstrate most if it.
When I try to do the following query: (SEE Query View.png)
Here is the SQL:
SELECT RawPatientVisitData.BPSys, PatientVisits.PatientVisitID, 1 AS Expr1
FROM RawPatientVisitData INNER JOIN (PatientList INNER JOIN PatientVisits ON PatientList.PatientID = PatientVisits.PatientID) ON RawPatientVisitData.PID = PatientList.PID;
This query results in the left part of this picture: (See Query View vs Patient Visits)
The problem is that PatientVisitID’s are repeated seemingly according to the number of times a patient had seen the clinic? But if I look at my PatientVisit table, it shows that each PatientVisitID is still unique to each patient and visit combo (See right side of picture)
I still appended the data to the VisitVitals table. When I did this, I got all of the vital results of a patient listed into the patient visitID that should be unique for one visit date.
See: Query Question.png
and then the next date also shows:
See: Query Question2.png
Why is it when I look under the patientlist subfields for the visits I see multiple PatientVisitID and test results being repeated on dates that they didn't occur on. Shouldn't each patientVisitID show only the tests that occured on that day?
Attachments
-
Query View vs. Patient Visits.jpg -
Query Question 2.png -
Relationships.png -
Query View.png -
Query Question.png
