Find Avg based on Date Range

[mpaulbattle]

Views: 6 Size: 444.0 KB">QCSTest.accdb

I am trying to find the average of satisfaction scores based on the date range entered. This would be run as a quarterly report.

I have created a query, qryQCS, and under the Date field in the criteria I have - Between [Start Date (mm/dd/yyyy):] And [End Date (mm/dd/yyyy):]

Is there a way to run a report based on this query with the selected dates to find the average of the information returned?

I did find in another thread, "Count days of month with Activity", where it was suggested
"Make Q1 to pull dates only ,set UNIQUE VALUES = true.
then make Q2 that uses Q1 to count."

Unfortunately, I am not sure what the user means or if this would even help. Could someone point me in the right direction please?

[aytee111]

Click on the Totals icon at the top and use that instead. You will group on your main fields, the date range will be "Where" and your scores will be "Avg"

[mpaulbattle]

QCSTest - Copy.zip
aytee111...I tried the totals but didn't give me what I needed, unless I did it wrong. However, I was able to generate a report and it is mostly what i needed. They have now asked if I am able to count the number of practices who gave us an 8-10, 5-7, or 1-4 within the selected date range. The only thing I have come up with is

8-10: =Count([Score]>=8)
5-7: =Count([Score]>=5 And [Score]<8)
1-4: =Count([Score]>=1 And [Score]<5)

It is just giving me the total count of all records. I have also tried =DCount("[Score]","qryQCS","[Score] >= 8") but i just get an #Error. Any suggestions?

[aytee111]

All of this can be done using the Totals button in queries. Not sure why it doesn't work for you.

[mpaulbattle]

The reason it doesn't work for me is because it groups it by the Mnemonic (Practice) so it will average the scores per mnemonic. We just need the avg for all participants.

[aytee111]

Remove the Mnemonic. This is YOUR query - you tell it what to do, not it telling you!

[mpaulbattle]

I have found what I needed. =Sum(IIf([Score]>=5 And [Score]<8,1,0)). Thanks for your responses aytee111. Your suggestion will come in handy in the future after we get the project in full swing.