How to 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.

 

[dhookom]

I would start by creating a union query to normalize your data.
SELECT FName, LName, ReviewDate, Department, 1 as QuestNum, [Quest1] as Response
FROM tblSurveys
UNION ALL
SELECT FName, LName, ReviewDate, Department, 2, [Quest2]
FROM tblSurveys
UNION ALL
SELECT FName, LName, ReviewDate, Department, 3, [Quest3]
FROM tblSurveys
UNION ALL
...etc for all questions...
You can then Create a Crosstab that groups by QuestNum as the Row Heading, Format(ReviewDate,"mmm") & Choose(Response +1,"N","Y") as the column heading and Count(Response) as the Value.


Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[robiddan]

Should a union and cross-tab query be done for all the questions? what if its over 200 questions?

 

[dhookom]

I'm not sure if a union query works for that many "iterations". I would probably group the questions into common categories for the purpose of the report.

You can find a complete, normalized, flexible survey demo at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]