Summary Report - multiple dates

  • Thread starter Thread starter pe66y1027
  • Start date Start date
P

pe66y1027

I have a summary report where I need a count of patients with their first
visit date within a specific date range, but I also need a count of those
same patients with one of four different outcomes during that same date range
(delivered, miscarriage, transferred out, or no outcome withing the date
range). Is this possible?
 
UNTESTED UNTESTED

SELECT Count(SELECT [XX].PatientID FROM YourTable AS [XX] GROUP BY
[XX].PatientID) AS Patient_Total, Sum(IIF([OutCome] = "Delivered", 1, 0) AS
[Delivered], Sum(IIF([OutCome] = "Miscarriage", 1, 0) AS [Miscarriage],
Sum(IIF([OutCome] = "Transferred out", 1, 0) AS [Transferred out],
Sum(IIF([OutCome] = "No outcome", 1, 0) AS [No outcome]
FROM YourTable
WHERE PatientID = (SELECT [YY].PatientID FROM YourTable AS [YY] WHERE
Min([YY].[VisitDate]) Between [Enter Stat] AND [Enter end] GROUP BY
[YY].PatientID )
GROUP BY YourTable.PatientID
HAVING YourTable.VisitDate Between [Enter Stat] AND [Enter end];
 
Back
Top