Summary Report - multiple dates

[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?

 

[KARL DEWEY]

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];