One MS Access Query - Multiple Averages?

[ajcke]

I'm new to Access (since Friday). I have a table called StudentScores which has the following fields - StudentScoresID, StudentID, Assessment, GradeLevel.

I need to find the average of scores from
Assessment1 from students in GradeLevel1
Assessment1 from students in GradeLevel2
Assessment2 from students in GradeLevel1
Assessment2 from students in GradeLevel2


and so on...

I plan on displaying this information in a report.

Can I do this in one query? If so how? What's the syntax? If not it looks like my only option is to create a query for each Average I need. If this is so I will have over 100 queries. Is there a better way to do this?

[Robeen]

Could you give us your Table field names & data types?

[ajcke]

Field Names: StudentScoreID(autonumber), StudentID(number), AssessmentName(text), GradeLevel(text), Score(number)

Do you need anything else?

[Robeen]

Try this:
1. Create a new Query.
2. Pull in AssessmentName, GradeLevel and Score.
3. Click the big yellow 'Totals' button towards the top-right of the Access window.
4. You should see 'Group By' under all three columns.
5. In the Score column, change the 'Group By' to 'Avg'.

Let us know if that works for what you need.

[ajcke]

Thanks. That worked! I was trying to over complicate things. The report looks great too. I forgot to mention that I also have a field in the same table called date. I realize I could further filter by adding the field date and add
Field: Date
Total: Where
Criteria: >=#1/1/2011# And <#12/31/2011#

...but then again I would have to create multiple queries to get a report this way. How would I go about allowing the user to select the year on the form from a combo box and then have a button that would run the report based on this selection?

[Robeen]

1. Your criteria:

>=#1/1/2011# And <#12/31/2011#

would exclude 12/31/2011.

2. Adding the unaltered Date to the above query would negate the grouping.

BUT . . .

If all you're interested in is the year part of the date for your results then try something like this:
1. Add the following field to your query:
Report_Year: Year([YourDateField])
2. In the criteria row of the 'Report_Year' field put this:
[Enter Your Report Year]

Whenever you run the query it should Prompt you with a dialog box saying 'Enter Your Report Year'.
If that works, you can run the report and each time you run it, you will be prompted by the query to provide a year for the data -> and the report will only display data for the year that is entered . . . [no need to get involved in contortions with buttons & VBA code on the Form - just let Access do the 'heavy lifting'].

I hope this helps!

[ajcke]

Actually I'm using the date to filter the year. Since it's a school year the dates would look more like
>=#7/1/2011# And <#6/30/2012#
would equal 2011-2012 school year.

I'm thinking when they run the report it would automatically choose the current year or there would be a combo box that would allow the user to select the correct year.

[ajcke]

I would need to somehow set something up that would do something similar to this. Not sure how to do this.
2011-2012 School Year equals >=#7/1/2011# And <#6/30/2012#
2012-2013 School Year equals >=#7/1/2012# And <#6/30/2013#

[ajcke]

Thanks for all your help Robeen! You answered my original question, so I created a new thread here for the trailing questions I've been asking.

[Robeen]

Hi ajcke!
I think you made the right decision by starting a new thread.
I'm glad I was able to help with your original question & I'm sure you'll get a solution for your next question!
All the best!