Design report with summary by month

[katedw203]

I can't believe I can't figure this out, but I am having trouble with a summary report. Basically, I have 1 table that captures a variety of information about a patient's visit to a clinic. in my table I have the following:

visit date
age
sex
language spoken
test result

I want to create a summary report that tells me, by month/year, a count of:
sex
language spoken
age (age group)

Additionally, we're interested in a count by month/year of some of those among those who tested positive for the test in question.

Does this make sense? It seems so simple, yet I can't figure it out.

Thanks!

[MMcCarthy]

You need to look at crosstab queries. This one should give you the count of male and female patients broken down by month.

TRANSFORM Count(patients.[PatientID]) AS NumPatients
SELECT patients.[Sex]
FROM patients
GROUP BY patients.[Sex]
PIVOT Format([VisitDate],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

[MMcCarthy]

This one includes the year in the grouping ...

TRANSFORM Count(patients.[PatientID]) AS NumPatients
SELECT patients.[Sex], Year(VisitDate) as [Year]
FROM patients
GROUP BY patients.[Sex], Year(VisitDate)
PIVOT Format([VisitDate],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");