468,539 Members | 1,624 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,539 developers. It's quick & easy.

MS Access Subquery - two most recent Tables results combined

1
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_code 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_CutoffDate = MRL_DaysInOutOfBounds)

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_DaysInOutOfBounds.

Eric L.

Here is my full code:

Expand|Select|Wrap|Line Numbers
  1.  
  2. 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
  3.  
  4. FROM (tblVisits LEFT JOIN tblLab ON tblVisits.chart_number = tblLab.chart_number) LEFT JOIN tblDemo ON tblVisits.chart_number = tblDemo.chart_number
  5.  
  6. 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"))
  7.  
  8. ORDER BY tblVisits.chart_number, tblLab.chart_number, tblVisits.visit_date, tblLab.service_date;
  9.  
  10.  
Jun 9 '10 #1
1 1934
jimatqsi
1,255 Expert 1GB
@lutz
I'm not sure what you mean by this
It also gives me a dialogue box asking for parameter values for MRL_ActualDate, and I don't want that period.

Do you mean you intend for the dialog box to pop up so you can supply the parameter MRL_ActualDate? If you mean you don't know why it is giving you the dialog box, that happens when you use an unknown name of a field; it thinks you want to supply that info at runtime so it pops up the dialog box so you can supply the value.

So, is MRL_ActualDate the correct name of the field in your table? Oh, maybe you need to qualify it, change MRL_ActualDate to XYZ.MRL_ActualDate where XYZ is the name of the table you intend to pull that from.
Jun 10 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Matthew Wells | last post: by
1 post views Thread by Barry | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.