Anybody??

[TonyTOCA]

I have the following problem


I'll give you an outline first. I have data in a delimited-textfile
from a labinstrument which i wanna add on a daily bases to my own
database used for storing labresults . The relevant tables in my
database are:
tbPatientInformation,: piID (pk), piLastName, piFirstName, piDOB etc
etc.
tbLabtest: ltID (pk), ltName, ltFullName, etc etc
tbTestResults: trID(pk), piID, ltID, trResult, trTestDate etc etc

I managed to import and extract the relevant data (using the
transfertext method) from the textfile into a temp table,
tbImportTemp, with the following fields : Patientname, Testname,
TestResult, TestDate. I then have another temp table, tbResultsTemp,
where i split the patientname into lastname and firstname using a
function (ParseWord, Allen Brown) in an APPEND query. So now i have a
table with the following fields: LastName, FirstName, Testname,
TestResult, TestDate. LastName and FirstName should go into
tbPatientInformation, after grouping them (1 patient can have many
tests). Testname, TestResult and TestDate should go into
tbTestResults. Testname from tbResultsTemp has the same values as
ltName from tbLabtest, but i can't insert it right away into
tbTestResults cuz i need the ltID corresponding to it.

My question is: how do i accomplish this-> Group the patients and then
insert LastName and FirstName into tbPatientInformation and at the
same time inserting the results (Testname, TestResult and TestDate)
into the results table with the appropriate piID and ltID.

Checking for duplicates will be done afterwards when the DOB's and the
rest of the patient information are being entered, but i'll get back
to this in another thread.

I would appreciate any help on this.

 

[solex]

Tony,
First your orginal datamodel appears to be a bit redundant, if I may I would
like to propose the following changes:
tbPatientInformation: piID, piLastName, piFirstName, piDOB...
tbLabtest: ltID, piID, ltTestName...
tbTestResult: trID, ltID, trTestDate, trResult...

To import I would like to make the following proposal for a sequence of
events

(1) Open up tbResults sorted by LastName, FirstName, TestName, TestDate
(2) Loop throught tbResults with conditional checks on changes in:
(a) lastname, firstname
(b) testname
(c) testdate
(3) on the change of each value listed in (2) call a function that would
return the appropriate id to be used in subsequent function calls. Function
prototypes:
Public Function InsertPatient(ByVal LastName As String, ByVal FirstName As
String, ...) As Long
Public Function InsertTest(ByVal TestName As String, ByVal PatientID As
Long, ...) As Long
Public Function InsertResult(ByVal TestResult As String, ByVal TestDate As
Date, ByVal TestID As Long, ...) As Long
(4) Each function would first check to see if a value already matches the
input arguments before inserting, but in either case the primary key would
be returned of the found record or the newly inserted record.
(5) Done.

Dan

 

[Tim Ferguson]

(e-mail address removed) (TonyTOCA) wrote in

I managed to import and extract the relevant data (using the
transfertext method) from the textfile into a temp table,
tbImportTemp, with the following fields : Patientname, Testname,
TestResult, TestDate

To be honest, if your lab is reporting something like

John Smith, Hb, 2004-09-28, 7.4

then you are already stuffed. The legal implications of ascribing this
result to the wrong John Smith are horrendous, not to mention the clinical
ones. You simply have to agree with your lab (or whoever creates the input
file in the first place) on a proper patient identifier.

You will then be in a position to translate this input file into a properly
designed table in Access. This should not be too hard, but at the moment
you are trying to do something that is not even theoretically possible.

Hope that helps


Tim F