Need to select Most Recent records. 1

[dmon000]

Hi,
I have two linked table, Patients and Diet Orders. In Diet orders there is a field which identifies each diet order as breakfast, lunch or dinner. Also in Diet Orders is a date field and a time field. For each patient and meal there may be more than one record with different times. What would a query look like that would select the just the latest records for all the patients?

 

[SkipVought]

Hi,

Select [i]other fields[/i], Max([DateField]+[TimeField])
From [TheTable]
Group By [i]other fields[/i]

Skip,

_[red][/red]

 

[PHV]

a date field and a time field
Why storing a SINGLE DateTime value in TWO fields ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dmon000]

Skip, Thanks for your reply.
I constructed the following query using your guideline. But I am not getting just the latest records for each patient. Maybe you can eyeball the code below and see where it is wrong. Thanks again! Dave

SELECT [ORDER DETAIL].MEAL, PATIENTS.[WARD ID], [ORDER DETAIL].[MPI #], [ORDER DETAIL].NURSE_ID, [ORDER DETAIL].[MEAL LOCATION], [ORDER DETAIL].[COMPONENT-1], [ORDER DETAIL].[COMPONENT-2], [ORDER DETAIL].[SPECIAL INSTRUCTIONS], [ORDER DETAIL].[ENSURE HI PRO], [ORDER DETAIL].BOOST, [ORDER DETAIL].[BOOST PUDDING], [ORDER DETAIL].[BOOST DIABETIC], [ORDER DETAIL].[TWO CAL], [ORDER DETAIL].[YOGHURT-LIGHT], [ORDER DETAIL].[YOGHURT-REGULAR], [ORDER DETAIL].[FF COTTAGE CHEESE], [ORDER DETAIL].OTHER, PATIENTS.[LAST NAME], PATIENTS.[FIRST NAME], PATIENTS.[FOOD ALLERGIES], PATIENTS.[NEW ADMIT], Max([meal-date]+[time]) AS Latest
FROM [ORDER DETAIL] INNER JOIN PATIENTS ON [ORDER DETAIL].[MPI #] = PATIENTS.[MPI #]
GROUP BY [ORDER DETAIL].MEAL, PATIENTS.[WARD ID], [ORDER DETAIL].[MPI #], [ORDER DETAIL].NURSE_ID, [ORDER DETAIL].[MEAL LOCATION], [ORDER DETAIL].[COMPONENT-1], [ORDER DETAIL].[COMPONENT-2], [ORDER DETAIL].[SPECIAL INSTRUCTIONS], [ORDER DETAIL].[ENSURE HI PRO], [ORDER DETAIL].BOOST, [ORDER DETAIL].[BOOST PUDDING], [ORDER DETAIL].[BOOST DIABETIC], [ORDER DETAIL].[TWO CAL], [ORDER DETAIL].[YOGHURT-LIGHT], [ORDER DETAIL].[YOGHURT-REGULAR], [ORDER DETAIL].[FF COTTAGE CHEESE], [ORDER DETAIL].OTHER, PATIENTS.[LAST NAME], PATIENTS.[FIRST NAME], PATIENTS.[FOOD ALLERGIES], PATIENTS.[NEW ADMIT]
HAVING ((([ORDER DETAIL].MEAL)=[Enter Meal]) AND ((PATIENTS.[WARD ID])=[Enter Unit]));

 

[SkipVought]

Could you explain what result you ARE getting?

Maybe post some sample data that demonstrates the issue?

Skip,

_[red][/red]

 

[SkipVought]

Try using WHERE clause rather than a HAVING clause...

SELECT
  ORD.MEAL
, PAT.[WARD ID]
, ORD.[MPI #]
, ORD.NURSE_ID
, ORD.[MEAL LOCATION]
, ORD.[COMPONENT-1]
, ORD.[COMPONENT-2]
, ORD.[SPECIAL INSTRUCTIONS]
, ORD.[ENSURE HI PRO]
, ORD.BOOST
, ORD.[BOOST PUDDING]
, ORD.[BOOST DIABETIC]
, ORD.[TWO CAL]
, ORD.[YOGHURT-LIGHT]
, ORD.[YOGHURT-REGULAR]
, ORD.[FF COTTAGE CHEESE]
, ORD.OTHER
, PAT.[LAST NAME]
, PAT.[FIRST NAME]
, PAT.[FOOD ALLERGIES]
, PAT.[NEW ADMIT]
, Max([meal-date]+[time]) AS Latest

FROM         [ORDER DETAIL] ORD
INNER JOIN   PATIENTS       PAT
   ON ORD.[MPI #]    = PAT.[MPI #]
[b]
WHERE ORD.MEAL       =[Enter Meal]
  AND PAT.[WARD ID]  =[Enter Unit]
[/b]
GROUP BY 
  ORD.MEAL
, PAT.[WARD ID]
, ORD.[MPI #]
, ORD.NURSE_ID
, ORD.[MEAL LOCATION]
, ORD.[COMPONENT-1]
, ORD.[COMPONENT-2]
, ORD.[SPECIAL INSTRUCTIONS]
, ORD.[ENSURE HI PRO]
, ORD.BOOST
, ORD.[BOOST PUDDING]
, ORD.[BOOST DIABETIC]
, ORD.[TWO CAL]
, ORD.[YOGHURT-LIGHT]
, ORD.[YOGHURT-REGULAR]
, ORD.[FF COTTAGE CHEESE]
, ORD.OTHER
, PAT.[LAST NAME]
, PAT.[FIRST NAME]
, PAT.[FOOD ALLERGIES]
, PAT.[NEW ADMIT]
;

Skip,

_[red][/red]

 

[dmon000]

Hi Skip,
Tried you latest query and it returns the same data. Here is a copy of that data (without all the fields!).

MEAL WARD ID MPI # FIRST NAME Latest
B 1 7155 RANDOLPH 5/2/2007 12:47:19 PM
B 1 7155 RANDOLPH 5/1/2007 8:59:51 PM
B 1 7737 SHAWN 5/1/2007 8:59:51 PM
B 1 12676 RICHARD 5/1/2007 8:59:51 PM
B 1 30037 ROLAND N 5/1/2007 8:59:51 PM
B 1 30864 STEVEN 5/1/2007 8:59:51 PM
B 1 43014 CAMILLE 5/1/2007 8:59:51 PM
B 1 44592 CHARLES 5/2/2007 1:41:59 PM
B 1 44592 CHARLES 5/1/2007 8:59:51 PM
B 1 53923 PEDRO 5/1/2007 9:31:44 PM
B 1 53923 PEDRO 5/1/2007 8:59:51 PM
B 1 85086 GERMAINE 5/1/2007 8:59:51 PM
B 1 104162 LAZARO 5/1/2007 8:59:51 PM
B 1 145151 JON-MICHAEL 5/1/2007 8:59:51 PM
B 1 151871 NATHANIEL 5/1/2007 8:59:51 PM
B 1 157480 EFRAIN 5/1/2007 8:59:51 PM
B 1 159221 OSCAR 5/1/2007 8:59:51 PM
B 1 162261 RYAN 5/1/2007 8:59:51 PM
B 1 164965 VICENTE 5/1/2007 8:59:51 PM
B 1 164970 MARIO 5/1/2007 8:59:51 PM
B 1 171245 PAUL 5/1/2007 8:59:51 PM

Thanks Again for your help!
Dave

 

[PHV]

select the just the latest records for all the patients
SELECT O.MEAL, P.[WARD ID], O.[MPI #], O.NURSE_ID, O.[MEAL LOCATION], O.[COMPONENT-1], O.[COMPONENT-2]
, O.[SPECIAL INSTRUCTIONS], O.[ENSURE HI PRO], O.BOOST, O.[BOOST PUDDING], O.[BOOST DIABETIC], O.[TWO CAL]
, O.[YOGHURT-LIGHT], O.[YOGHURT-REGULAR], O.[FF COTTAGE CHEESE], O.OTHER
, P.[LAST NAME], P.[FIRST NAME], P.[FOOD ALLERGIES], P.[NEW ADMIT], O.[meal-date], O.[time]
FROM ([ORDER DETAIL] AS O
INNER JOIN PATIENTS AS P ON O.[MPI #] = P.[MPI #])
INNER JOIN (
SELECT [MPI #], Max([meal-date]+[time]) AS Latest FROM ORD GROUP BY [MPI #]
) AS L ON O.[MPI #] = L.[MPI #] AND (O.[meal-date] + O.[time]) = L.Latest

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886