I fully admit, I am begging for some help. I am at the mercy of anyone's generous nature. I wish I had a template to follow for this one. I thank you in advance for anyone's advice.
Maybe this is not possible to do in one query?
I know I screwed up the Where clause, and probably the placement of the subquery SELECT statements, but since I am doing this out of design view in Access, the SQL with all its glorious [] and () automatically generates, I cannot tell what I screwed up.
It also gives me a dialogue box asking for parameter values for MRL_ActualDate, and I don't want that period.
What I want to achieve:
1. First, I pull back a list containing the most recent patient visit, for each patient, in a given quarter, say 01/01/2010-3/31/2010, complete with the patients' chart number, and visit date.
2. Second, I pull back a list containing the most recent hemoglobin lab work (ha1c) for each patient, (for the entire length of the database, 1/1/2004 to 3/31/2010), complete with the patients'
chart number,
date of the most recent lab (tblLab.service _date AS MRL_ActualDate) ,
lab name (tblLab.raw_cod e AS LabCode),
the lab result (tblLab.result AS LabTest_Result) ,
then I put the lab result in a frequency distribution (the IIF statement called Lab_Ranges)
then I create a calculation to determine whether the ha1c test is within or out of bounds as a valid test.
Its a six month window from the most recent visit date, minus 183 days is what I used.
I subtract the six month cutoff date from the actual and most recent lab date of the test for each patient (MRL_ActualDate minus 6M_MRL_CutoffDa te = MRL_DaysInOutOf Bounds)
3. From these two temporary tables, I want to pull back the entire unique visit table list of each patients' most recent visit within the quarter (#1 above) and all the matching data from the labs table (#2 above) where the chart numbers equal.
4. the demographic table is simply to supply the last and first names of the patient and a phone number, to have someone in customer service call a patient to come back for tests if they are in danger of an old or outdated test, which are negative or low postive numbers for the calc in MRL_DaysInOutOf Bounds.
Eric L.
Here is my full code:
Maybe this is not possible to do in one query?
I know I screwed up the Where clause, and probably the placement of the subquery SELECT statements, but since I am doing this out of design view in Access, the SQL with all its glorious [] and () automatically generates, I cannot tell what I screwed up.
It also gives me a dialogue box asking for parameter values for MRL_ActualDate, and I don't want that period.
What I want to achieve:
1. First, I pull back a list containing the most recent patient visit, for each patient, in a given quarter, say 01/01/2010-3/31/2010, complete with the patients' chart number, and visit date.
2. Second, I pull back a list containing the most recent hemoglobin lab work (ha1c) for each patient, (for the entire length of the database, 1/1/2004 to 3/31/2010), complete with the patients'
chart number,
date of the most recent lab (tblLab.service _date AS MRL_ActualDate) ,
lab name (tblLab.raw_cod e AS LabCode),
the lab result (tblLab.result AS LabTest_Result) ,
then I put the lab result in a frequency distribution (the IIF statement called Lab_Ranges)
then I create a calculation to determine whether the ha1c test is within or out of bounds as a valid test.
Its a six month window from the most recent visit date, minus 183 days is what I used.
I subtract the six month cutoff date from the actual and most recent lab date of the test for each patient (MRL_ActualDate minus 6M_MRL_CutoffDa te = MRL_DaysInOutOf Bounds)
3. From these two temporary tables, I want to pull back the entire unique visit table list of each patients' most recent visit within the quarter (#1 above) and all the matching data from the labs table (#2 above) where the chart numbers equal.
4. the demographic table is simply to supply the last and first names of the patient and a phone number, to have someone in customer service call a patient to come back for tests if they are in danger of an old or outdated test, which are negative or low postive numbers for the calc in MRL_DaysInOutOf Bounds.
Eric L.
Here is my full code:
Code:
SELECT tblVisits.chart_number, tblDemo.last_name, tblDemo.first_name, tblDemo.phone1, tblDemo.phone2, tblVisits.visit_date, [visit_date]-91 AS 3M_MRL_CutoffDate, [visit_date]-183 AS 6M_MRL_CutoffDate, tblLab.service_date AS MRL_ActualDate, tblLab.raw_code AS LabCode, tblLab.result AS LabTest_Result, IIf([LabTest_Result]<"7","<7",IIf([LabTest_Result]>"9",">9",IIf([LabTest_Result] Between "7" And "9","7 to 9"))) AS Lab_Ranges, [MRL_ActualDate]-[6M_MRL_CutoffDate] AS MRL_DaysInOutOf_Bounds FROM (tblVisits LEFT JOIN tblLab ON tblVisits.chart_number = tblLab.chart_number) LEFT JOIN tblDemo ON tblVisits.chart_number = tblDemo.chart_number WHERE (((tblVisits.visit_date) Between #1/1/2010# And #3/31/2010# And (tblVisits.visit_date)=(SELECT Max(visit_date) FROM [tblVisits] as TEMP WHERE Temp.chart_number = [tblVisits].chart_number)) AND ((tblLab.service_date) Between #1/1/2004# And #3/31/2010# And ([tblLab].[MRL_ActualDate])=(SELECT Max(MRL_ActualDate) FROM [tblLab] as TEMP2 WHERE TEMP2.chart_number = [tblLab].chart_number)) AND ((tblLab.raw_code)="ha1c") AND ((tblDemo.clinic_code)="1") AND ((tblVisits.clinic_code)="1") AND ((tblLab.clinic_code)="1")) ORDER BY tblVisits.chart_number, tblLab.chart_number, tblVisits.visit_date, tblLab.service_date;
Comment