This is not homework, I actually do this for a living, but I'm having brain freeze on this one and I'm swamped with work right now. The code below will create a table with sample data.
The data is for a group of patients. There is a table that has dozens of lab tests for the patients. Dozens of tests covering dozens of dates. Each patient can have more than one of the same test on a given date and multiple of the same tests through out the year.
Normally, finding the last date or the highest value for a specific test across a sub-set of patients is not an issue. In the case of a particular pair of tests I must consider them to be the same test and I may be called on to find the last one or the one with the highest or lowest value in a date range.
I wrote code to do this, but it runs slow. I want to come up with away to do this only with queries so I can speed up the process by taking advantage of the Jet engine.
The two tests are LDL and LDL (Direct). All of the tests dates in the table will be for 2007. I need to find the last test of either test - but only one - for each patient. In some cases there are multiple of each on the same date, and in that case I need the test with the lowest value. The multiples on the same date could be the same test or they could be one or more of each test with different values.
Anyone?
The data is for a group of patients. There is a table that has dozens of lab tests for the patients. Dozens of tests covering dozens of dates. Each patient can have more than one of the same test on a given date and multiple of the same tests through out the year.
Normally, finding the last date or the highest value for a specific test across a sub-set of patients is not an issue. In the case of a particular pair of tests I must consider them to be the same test and I may be called on to find the last one or the one with the highest or lowest value in a date range.
I wrote code to do this, but it runs slow. I want to come up with away to do this only with queries so I can speed up the process by taking advantage of the Jet engine.
The two tests are LDL and LDL (Direct). All of the tests dates in the table will be for 2007. I need to find the last test of either test - but only one - for each patient. In some cases there are multiple of each on the same date, and in that case I need the test with the lowest value. The multiples on the same date could be the same test or they could be one or more of each test with different values.
Anyone?
Code:
Public Sub LabTest()
Dim k As Integer
Dim i As Integer
Dim sChartNumber As String
Dim sTestName As String
Dim iValue As Integer
Dim dTestDate As Date
Dim dTestDateOld As Date
DoCmd.RunSQL "SELECT '96523' AS ChartNumber, #1/1/2008# AS LabDate, 65 AS LabValue, 'LDL' AS ItemName INTO tblLabTest"
Randomize
With CurrentDb.OpenRecordset("tblLabTest", dbOpenDynaset)
For k = 1 To 50
sChartNumber = Trim(Str(CInt((32000 * Rnd) + 1)))
For i = 1 To 8
Select Case i
Case 1
sTestName = "AST"
Case 2
sTestName = "MicroAl"
Case 3
sTestName = "HemoglbinA1c"
Case 4
sTestName = "Potasium"
Case Else
If CInt((32000 * Rnd) + 1) Mod 2 = 0 Then
sTestName = "LDL"
Else
sTestName = "LDL (Direct)"
End If
End Select
iValue = CInt((150 * Rnd) + 1)
dTestDateOld = dTestDate
dTestDate = DateSerial(2007, CInt((12 * Rnd) + 1), CInt((28 * Rnd) + 1))
If k > 25 And k < 30 Then
If i Mod 2 = 0 Then
Debug.Print sChartNumber
dTestDate = dTestDateOld
End If
End If
.AddNew
!ChartNumber = sChartNumber
!LabDate = dTestDate
!LabValue = iValue
!ItemName = sTestName
.Update
Next
Next
.Close
End With
End Sub
.... For the life of me I could not see where the code you posted was accomplishing the task at hand!!! ... but finally you turned on the switch in my brain that said ... HEY you doof, the code creates a sample table for you!!! .... DOH!!! .... 