By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,486 Members | 2,067 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,486 IT Pros & Developers. It's quick & easy.

Design report with summary by month

P: 2
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!
Jan 8 '10 #1
Share this Question
Share on Google+
2 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
You need to look at crosstab queries. This one should give you the count of male and female patients broken down by month.

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Count(patients.[PatientID]) AS NumPatients
  2. SELECT patients.[Sex]
  3. FROM patients
  4. GROUP BY patients.[Sex]
  5. PIVOT Format([VisitDate],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
  6.  
Jan 8 '10 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
This one includes the year in the grouping ...

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Count(patients.[PatientID]) AS NumPatients
  2. SELECT patients.[Sex], Year(VisitDate) as [Year]
  3. FROM patients
  4. GROUP BY patients.[Sex], Year(VisitDate)
  5. PIVOT Format([VisitDate],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
  6.  
Jan 8 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.