How do i create monthly access reports 1

[robiddan]

Hi, i have a database and i need your expertise. How do i create a monthly/weekly/yearly report from a database based on:
Demographics i.e.,

FName; LName; ReviewDate; Department;
Quest1 (Yes/No/%); Quest2(Yes/No/%); Quest3(Yes/No/%) etc...

NB:
I use Number as Data Type
Row Source = 1;"YES";0;"NO"

To calculate the %age, it is (Yes+No)*100

Questions i.e., (Quest1; Quest2;Quest3)
Y/N
1) Was the patient satisfied? 1
2) Was the patient an Adult ? 0
3) Did the patient return ? 1

At the end of the Data Entry session i want to run a report based on the ReviewDate. At the end of the 3rd month or so i would like to run a monthly report from January to present date for the questions.

The report should look like the following based on the amount of Yes or No that the data entry persons entered.

Departmental report for [DEPARTMENT]
From [ReviewDate_Begining] To [ReviewDate_Ending]

January February March Total
Y N % Y N % Y N % Y N %
1)Was the pt satisfied?10 0 100 10 0 100 6 4 60 26 4 86
2)Was the pt an Adult? 0 10 0 0 10 0 6 4 60 6 24 20
3)Did the pt return?1 6 4 60 6 4 60 6 4 60 18 12 60

I would like to create this report without using sub-reports for each month. Is there any other way that i could create these monthly reports. Would cross-tab queries help. If so, please show me. Thank you very much.

 

[DoubleD]

No need for a crosstab or subreports.
Write an aggregate query.
Group By Month(ReviewDate)
Sum(IIF(Quest1="YES",1,0))
Sum(IIF(Quest1="NO",1,0))
Sum(IIF(Quest2="YES",1,0))
Sum(IIF(Quest2="NO",1,0))
Sum(IIF(Quest3="YES",1,0))
Sum(IIF(Quest3="NO",1,0))

Now in your query, set the number of columns you want.
Your percentage can be calculated in the report.


I am what I am based on the decisions I have made.

DoubleD