Luis Cazares (8/21/2014)
I'd use a cross tabs approach which seems more flexible, but that's up to you.
SELECT YEAR(DateFormCompleted) AS YearRecruited,
Hospital,
COUNT(CASE WHEN month(DateFormCompleted) = 1 THEN PersonCounter END) AS 'Jan',
COUNT(CASE WHEN month(DateFormCompleted) = 2 THEN PersonCounter END) AS 'Feb',
COUNT(CASE WHEN month(DateFormCompleted) = 3 THEN PersonCounter END) AS 'Mar',
COUNT(CASE WHEN month(DateFormCompleted) = 4 THEN PersonCounter END) AS 'Apr',
COUNT(CASE WHEN month(DateFormCompleted) = 5 THEN PersonCounter END) AS 'May',
COUNT(CASE WHEN month(DateFormCompleted) = 6 THEN PersonCounter END) AS 'Jun',
COUNT(CASE WHEN month(DateFormCompleted) = 7 THEN PersonCounter END) AS 'Jul',
COUNT(CASE WHEN month(DateFormCompleted) = 8 THEN PersonCounter END) AS 'Aug',
COUNT(CASE WHEN month(DateFormCompleted) = 9 THEN PersonCounter END) AS 'Sep',
COUNT(CASE WHEN month(DateFormCompleted) = 10 THEN PersonCounter END) AS 'Oct',
COUNT(CASE WHEN month(DateFormCompleted) = 11 THEN PersonCounter END) AS 'Nov',
COUNT(CASE WHEN month(DateFormCompleted) = 12 THEN PersonCounter END) AS 'Dec',
COUNT(PersonCounter) AS 'Total'
FROM tblPerson p
JOIN tblManagementOfBleeding m ON m.SubjectNumber = p.SubjectNumber)x
GROUP BY YEAR(DateFormCompleted),
Hospital
Thank you Luis - that works with one minor change - see below. Thanks also to the others who tried to help. Sorry I didn't post what you requested; work has kept me busy recently.
This:
JOIN tblManagementOfBleeding m ON m.SubjectNumber = p.SubjectNumber)x
to this:
JOIN tblManagementOfBleeding m ON m.SubjectNumber = p.SubjectNumber