Ryan:
I hate to be the ghost at the feast as its clear you've invested a lot of
time and effort in getting this far, but on the basis that 'the first thing
to do when in a hole is to stop digging' I do feel I should draw your
attention to the flaws in your model.
The problem here is that you've included separate columns in PatientTable
for each category of clinical assessment, Mucous_membrane, Eye, Ear etc.
This is what is known as 'encoding data as column headings'. Each column
represents a data value of an attribute, AssessmentCategory say, of an entity
type ClinicalAssessmentCategories say (you may well have better terms for
them, but that's bye-the-bye). A fundamental principle of the database
relational model (the 'information principle) is that data is stored as
explicit values at column positions at rows in tables.
The correct way to model this would be to have a
ClinicalAssessmentCategories table, with column AssessmentCategory. Each row
in this table would represent one category of clinical assessment.
A PatientReviews table recording each patient review would have foreign keys
PatientID and DoctorID, each referencing the primary key of tables Patients
and Doctors respectively, and a column ReviewDate. In combination these
would form the table's composite primary key. This table would also contain
a Comments column as this is specific to each patient review rather than the
individual clinical assessments within that review.
To record the individual clinical assessments per patient review would
require a PatientReviewDetails table. This would have composite foreign key
of PatientID, DoctorID and ReviewDate referencing the composite primary key
of PatientReviews. In addition it would have another foreign key column
AssessmentCategory referencing the primary key of
ClinicalAssessmentCategories and a column AssessmentResult say to record the
result of each assessment.
As regards the interface one approach would be to have form based on
PatientReviews with combo boxes bund to the PatientID and DoctorID columns
and text box controls bound to the ReviewDate and Comments columns. Within
this for would be a continuous form view subform based on
PatientReviewDetails and liked to the parent form by having
PatientID;DoctorID;ReviewDate as the subform control's LinkMasterFields and
LinkChildFields properties.
Once the patient, doctor and review date have been entered in the parent
form its then merely a question of inserting one row into the subform for
each category of clinical assessment undertaken in the review.
When a new review is undertaken it would be possible to automatically insert
rows into the PatientReviewDetails table with values to match the last review
for this patient if desired so that each can then be left as is, or changed
as appropriate. This could be done in code in the parent form's module like
so:
Const conMESSAGE = _
"A doctor, patient and review date must be entered."
Dim cmd As ADODB.Command
Dim strSQL As String
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
' first ensure all required values are present
If IsNull(Me.DoctorID) Or IsNull(Me.PatientID) _
Or IsNull(Me.ReviewDate) Then
MsgBox, conMESSAGE, "Invalid Operation"
Exit Sub
End If
'ensure current record is saved
Me.Dirty = False
' insert new rows into table with same values as latest
' review for current doctor/patient
strSQL = "INSERT INTO PatientReviewDetails" & _
"(ReviewDate,DoctorID, PatientID," & _
"AssessmentCategory,AssessmentResult) " & _
"SELECT #" & Format(Me.ReviewDate,"yyyy-mm-dd") & "#." & _
"DoctorID, PatientID,AssessmentCategory,AssessmentResult " & _
"FROM PatientReviewDetails " & _
"WHERE PatientID = " & Me.PatientID & _
" AND DoctorID = " & Me.DoctorID & _
" AND ReviewDate = " & _
"(SELECT MAX(ReviewDate) " & _
"FROM PatientReviewDetails " & _
"WHERE PatientID = " & Me.PatientID & _
" AND DoctorID = " & Me.DoctorID & ")"
cmd.CommandText = strSQL
cmd.Execute
' requery subform to show new rows
Me.sfcPatientReviewDetails.Requery
where sfcPatientReviewDetails is the name of the subform control in the
parent form's Controls collection.
As well as being theoretically sound the model described above does of
course have practical implications in that should the users wish to amend the
basis of clinical assessment, e.g. by adding a new category or renaming an
existing category this is merely a case of editing the
ClinicalAssessmentCategories table, for which provision cam be made in the
interface. By 'encoding the data as column headings' on the other hand would
require an amendment both of the table definition and of your code which
assigns values to the columns.
Ken Sheridan
Stafford, England
ryguy7272 said:
Alllll Rightyyy Then!! This is starting to make sense now. Bob, how do i
implement what you proposed? The line add a column and set the value to
'nz(max("ReviewId","ReviewTable","PatientID= " & me.patientID"
in code, when you add the Review record' sounds like it will work. Can you
walk me through the process; step by step? Conceptually, it makes sense, in
reality, I don't believe I know how to do this. I now have one Table named
tblStatusCheck, whcihc has all the data points for the review for patients.
This is linked to my 'Patienttable' which has all relevant information for
patients. The join line is 'include ALL records from the PatientTable and
only those records from the tblStatusCheck where the joined fields are
equal'. Please walk me through the next step or two. I believe this is the
last major obstacle that I need to deal with; I'd like to get a handle on
this soon.
Thanks so much!
Ryan--
--
RyGuy
Bob Quintal said:
in
Oh no! I think I've really hit a wall this time. I'm back to my
original design, with 8 tables. I can easily filter for nulls in
a Query, and display all relevant records in a SubForm, but I
can't, for the life of me, figure out how I'm going to get a Form
to know which table to add new records to!! If the doctor has
seen the patient 1 time, I can easily display the patient-reviews
in a SubForm, but how can I control the main Form so that it adds
the results of the 2nd review to the 2nd Table? Below is the code
that sends the results of the patient-review to the 1st Table:
No! No! No!!!!!!!!
Only use 1 table! 1 Table! 1 Table!
You add a new column for the ReviewID, and set the value of this
column to
nz(max("ReviewId","ReviewTable","PatientID= " & me.patientID"
in code, when you add the Review record.
And you should split that table to hold the patient's common info
(name, address, dob, etc.),, with a separate table for the review
criteria, plus the patient ID which is the key into the first table.
Private Sub Command19_Click()
Dim err As Integer
Dim cnn1 As ADODB.Connection
Dim rstPatientTable As ADODB.Recordset
Dim strCnn As String
'Dim mydb As DAO.Database
Dim mydb As String
'if no errors insert data
If err < 1 Then
' Open a connection.
Set cnn1 = New ADODB.Connection
mydb = "C:\RTDA Tool.mdb"
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mydb
cnn1.Open strCnn
' Open contact table.
Set rstPatientTable = New ADODB.Recordset
rstPatientTable.CursorType = adOpenKeyset
rstPatientTable.LockType = adLockOptimistic
'rstPatientTable.Open "PatientTable", cnn1, , , adCmdTable
rstPatientTable.Open "SELECT * FROM PatientTable WHERE
(((PatientTable.MR)=" & Me!MR & "));", cnn1
'get the new record data
rstPatientTable.Update
rstPatientTable!Skin = Skin
rstPatientTable!Mucous_Membrane = Mucous_Membrane
rstPatientTable!Eye = Eye
rstPatientTable!Ear = Ear
rstPatientTable!Salivery_Gland = Salivery_Gland
rstPatientTable!Pharynx_and_Esophogus =
Pharynx_and_Esophogus rstPatientTable!Larynx = Larynx
rstPatientTable!Upper_GI = Upper_GI
rstPatientTable!Lower_GI_Including_Pelvis =
Lower_GI_Including_Pelvis rstPatientTable!Lung = Lung
rstPatientTable!Genitourinary = Genitourinary
rstPatientTable!Heart = Heart
rstPatientTable!CNS = CNS
rstPatientTable!WBC = WBC
rstPatientTable!Platelets = Platelets
rstPatientTable!Neutrophis = Neutrophis
rstPatientTable!Hemoglobin = Hemoglobin
rstPatientTable!Hematocrit = Hematocrit
rstPatientTable!Current_Weight = Current_Weight
rstPatientTable!Assessment_Date = Assessment_Date
rstPatientTable!StatusCheck_Comments =
StatusCheck_Comments
rstPatientTable.Update
' Show the newly added data.
MsgBox "Patient: " &
Me!SearchStatusCheckSubForm.Form.[FirstName] &
" " & Me!SearchStatusCheckSubForm.Form.[LastName] & " has been
successfully updated!!"
'close connections
rstPatientTable.Close
cnn1.Close
Else
MsgBox "An Error has occurred, please check and try again"
End If
End Sub
I'd appreciate any thoughts or insights on this.
Thanks,
Ryan---
