Averages

[Muzicmn969]

any ideas???

tasked with creating a new database for a school i came across this problem. Wanted to see if you guys had any ideas before i even begin creating this thing.

School has grades 1 thru 8. All students in all grades do not take the same amount of classes...thus my problem. How can i create a DB to track and average out student grades??? some kids in grade three might take 6 classes while others may take 7 or 8. So my deciding factor used to calculate my averages must be dynamic. Or in someway have Access query the student and add all the records and use that as a divider to get the average.....aaaahhhhh ! ! ! !

there must be an easier way

any ideas??

 

[dan-cat]

Here is something to get you going.

I've created a table detailing the kiddies details. Then another detailing the classes that the kiddie is taking. Tables are linked via a one to many relationship. The calculated field 'txtAvg' on the main form details the average score on update of the score field

 

[dan-cat]

Now the moderator is multiposting

 

[Muzicmn]

that works great.....

you guys make it seem so simple

thanks

Ricky

 

[dan-cat]

No worries

 

[Mile-O]

Now the moderator is multiposting

Excuse me?

 

[Muzicmn]

hummmmm, another question.

the prog you gave me does a great job in averaging that student. to take this a little further, do you know of anyway i can average an entire class??

im guessing using the DAvg statement you used but i cant seem to figure out how to make it give me the average for all students and then for the entire class

thanks again

Ricky

 

[dan-cat]

Excuse me?

We got the same thread in two different forums

 

[dan-cat]

hummmmm, another question.

the prog you gave me does a great job in averaging that student. to take this a little further, do you know of anyway i can average an entire class??

im guessing using the DAvg statement you used but i cant seem to figure out how to make it give me the average for all students and then for the entire class

thanks again

Ricky

Ok just need to make sure I'm understanding the US education set-up.
A grade equals a whole years worth of students.
A class equals a grade worth of students for a particular subject

You would need to assign a student to a particular class. Once you have assigned all your students to all their classes you can create a query which will detail all the scores for a particular class. You can then calculate your average from there. If you get stuck give us a shout back

 

[Pat Hartman]

Rather than using the domain function, DAvg() which requires that you run the function for each set of data you want to average, use a Totals query. You can create all the averages in a single query and use the query as the recordsource for your report. Or, if you want to show detail and summary data, calculate the averages in the reports. Access works out the mechanics of how to calculate the average. When you build the report with the wizard, you will see options that allow you to specify aggregate calculations such as Sum() and Avg().

 

[Mile-O]

We got the same thread in two different forums

No we don't. There's a redirect in the General forum as I moved this thread from there.

 

[dan-cat]

I had the forum to myself this weekend. Must have got over-confident replying too all those threads.

Pat comes back and shows a much better way to calculate averages and you come back showing my lack of forum etiquette.

But as John Lydon says: "You can't beat me!"

 

[Muzicmn]

im still stumped....

this db needs to be able to give me several averages.... but the important thing to keep in mind is the fact that not all students in the same grade take the same amount of classes...for instance a 3rd grader might take 5 classes while another third grader takes 7. this creates the problem of having access determine the divider by adding how many classes the student has grades for

the several averages i need are as follows

i need the average of the kid and all his classes for all individual report card periods and for the year, all report card periods put together

i also need to average all the students in a teachers class to determine how the class as a whole is doing

i can create the queries but im stumped on having access determine the divider per studnet.

thanks ahead of time

Ricky

I tried using the DAvg in the report, it gives me the average ALL the students scores in the report, will work if i run the report for all students individually, but then i will be running the report several hundred times

i also tried the Avg statement, same problem, unless my format is incorrect

I managed to get it to work in Excel by using the countif feature, but prefer to make it in access......i guess what i want to do is probably set a divider of 9 (the most classes offered) and have access change the divider by subtracting 1 for ever grade that is null......but not really sure on how to write that into code

 

[Pat Hartman]

Select StudentID, Avg(Grade) As GradeAvg
From YourTable
Group by StudentID;

The above query will produce a correct average for each student regardless of the number of grades per student.

 

[Muzicmn]

Dont know what to say.....


works great.....wish i could have figured it out tho'

thanks a million you all have made my life sooooo much easier

Ricky