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?

 

[Steve]

Yes!

Create a query that includes PatientID, FirstVisitDate and Outcome. Use the
Between ... And construct in the FirstVisitDate field criteria. Click on the
Sigma (looks like capital E) button in the menu at the top of the screen.
Under PatientID, change Group By to Count. This query will now return the
count of patients with each of the four different outcomes during the date
range you specify in the criteria of FirstVisitDate.

To get the count of patients with their first visit date within the date
range you specified in the criteria of FirstVisitDate, use running sum to
sum the counts of all the outcomes.

Steve
(e-mail address removed)

 

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