Excel formulas in Access

[tom035]

I have the following formula in an excel spreadsheet and would like to use the same concept in Access:

=$I25+((((INDIRECT($D$5&"!M25"))+(INDIRECT($D$6&"!M25"))+(INDIRECT($D$7&"!M25"))+(INDIRECT($D$8&"!M25"))+(INDIRECT($D$9&"!M25"))+(INDIRECT($D$10&"!M25"))+(INDIRECT($D$11&"!M25"))+(INDIRECT($D$12&"!M25"))+(INDIRECT($D$13&"!M25"))+(INDIRECT($D$14&"!M25"))+(INDIRECT($D$15&"!M25"))+(INDIRECT($D$16&"!M25"))+(INDIRECT($D$17&"!M25"))+(INDIRECT($D$18&"!M25"))+(INDIRECT($D$19&"!M25"))+(INDIRECT($D$20&"!M25"))+(INDIRECT($D$21&"!M25"))+(INDIRECT($D$22&"!M25"))+(INDIRECT($D$23&"!M25"))+(INDIRECT($D$24&"!M25"))+(INDIRECT($D$25&"!M25")))/$A25)-100)

This is a formula which uses references and averages. Is there any way to simplify something like this in Access?

For example, I want to average a set of numbers, and then use those averages to figure out new averages. I can go into more detail if need be.

 

[lespaul]

Let's say you have a database that stores students, assignments and grades:
[tt]
tGrades
StudentID AssignmentID Score
1 1 65
1 2 85
2 1 80
2 2 66

tStudent
StudentID Name
1 Joe Blow
2 Jane Doe

tAssignment
AssignmentID AssignmentName PossiblePoints
1 Test 100
2 Homework 100
[/tt]
So you want each student's average:

SELECT Name, AVG(Score) As AverageGrade FROM tGrades G 
INNER JOIN tStudent S ON G.StudentID = S.StudentID
GROUP BY Name

[tt]
Results:
Name AVerageGrade
Joe Blow 75
Jane Doe 73
[/tt]
To answer your question about averaging the averages, you can do that this way. Save the above query as StudentAverages and then use that as the source of another query:

SELECT Avg(AverageGrade) FROM [b]StudentAverages[/b]

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi