Comparing averages

  • Thread starter Thread starter Mark Houghton
  • Start date Start date
M

Mark Houghton

My second question today on comapring averages, but.....

I have a database of assessment scores for two year groups (9 and 10) of
students.

For year 9:
I want to calculate (and chart) the average score for a boy, the average
score for a girl (I can do this so far), and on the same chart, the average
score for all students in year 9 (ie boys and girls combined average). It's
the last bit I can't achieve.

Once again, if there's a better newsgroup, let me know. gettingstarted
seemed right!

TIA.
M.

--
From Sunny South Canterbury

Once upon a time I tried not to spoil my reply address.
Then the Swen-virus started to send me emails :-(
Remove mashed to reply. Sorry.
 
Though it may be a bit slow, check out the DAvg domain function to return
the average of all students as a field in each record.
 
And an alternative to this could be to create a query that returns just the
Average value, and then add that table (with no join to the other table) to
your query, and include the Average value field in the query.
 
Ken Snell said:
And an alternative to this could be to create a query that returns just the
Average value, and then add that table (with no join to the other table) to
your query, and include the Average value field in the query.
Click to expand...


I just know that this is a silly question, but I've been staring at this too
long. I can create a query showing the boys' averages and the girls'
averages. I can create another one which shows the averages of the students
(boys and girls combined). I can't - and this is the silly bit! - create a
query which shows both together (and hence, I can't graph them alongside
each other).

If I have something like this:

Fred 10
Tim 10
Jane 12
Tina 8
Karen 13

I need to be able to get (in one query)
Boys' average: 10
Girls' average: 11
Overall average: 10.6

If I could achieve that, then I've cracked it (this part anyway).

I hope that this is reasonably clear!
M.
 
Ken Snell said:
And an alternative to this could be to create a query that returns just the
Average value, and then add that table (with no join to the other table) to
your query, and include the Average value field in the query.
Click to expand...

I just know that this is a silly question, but I've been staring at this too
long. I can create a query showing the boys' averages and the girls'
averages. I can create another one which shows the averages of the students
(boys and girls combined). I can't - and this is the silly bit! - create a
query which shows both together (and hence, I can't graph them alongside
each other).

If I have something like this:

Fred 10
Tim 10
Jane 12
Tina 8
Karen 13

I need to be able to get (in one query)
Boys' average: 10
Girls' average: 11
Overall average: 10.6

If I could achieve that, then I've cracked it (this part anyway).

I hope that this is reasonably clear!
M.
 
Try this:

SELECT Avg(Tablename.GradeField) AS AvgAllGrades,
(SELECT Avg(T.GradeField) As AvgB
FROM Tablename AS T
WHERE T.BoyGirlField = "Boy") AS AvgBoys,
(SELECT Avg(TT.GradeField) As AvgG
FROM Tablename AS TT
WHERE TT.BoyGirlField = "Girl") AS AvgGirls
FROM TableName;
 
Ken Snell said:
Try this:

SELECT Avg(Tablename.GradeField) AS AvgAllGrades,
(SELECT Avg(T.GradeField) As AvgB
FROM Tablename AS T
WHERE T.BoyGirlField = "Boy") AS AvgBoys,
(SELECT Avg(TT.GradeField) As AvgG
FROM Tablename AS TT
WHERE TT.BoyGirlField = "Girl") AS AvgGirls
FROM TableName;
Click to expand...

Wow, It works! Terrific - thanks. I can even adapt it. I've not used SQL
before.
Nor did it occur to me I might need it. Would you mind running through the
code? (though it's already done what I want, so it's just for interest).

Thanks again.
M.

--
From Sunny South Canterbury

Once upon a time I tried not to spoil my reply address.
Then the Swen-virus started to send me emails :-(
Remove mashed to reply. Sorry.
 
The first select clause returns the average of all grades. If we delete all
the subqueries, you have this:

SELECT Avg(TableName.GradeField) AS AvgAllGrades,
FROM TableName;

That is a normal totals query that returns the average of the GradeField for
all records (no filtering).

You then wanted a single value for the boys' grades. Using the same logic as
above, but with a filter for boys:

SELECT Avg(T.GradeField) As AvgB
FROM TableName AS T
WHERE T.BoyGirlField = "Boy";

In the above, I am using T as an alias for the table name. Although not
required when the query is run on its own, subqueries often require the use
of aliases so that Jet knows which tables go where. It's also needed in this
case because, when this is used as a subquery to the first one (top of this
post), Jet needs to know that there are two copies of TableName table being
used and they are not to be the same.

Similarly for the girls' average grade:

SELECT Avg(TT.GradeField) As AvgG
FROM TableName AS TT
WHERE TT.BoyGirlField = "Girl";

So, in order to display the single value from both the boys' average and the
girls' average, we use each subquery as a field in the query, and we put
each subquery inside ( ) so that Jet knows to evaluate it separate from the
rest of the query.

SELECT Avg(TableName.GradeField) AS AvgAllGrades,
(SELECT Avg(T.GradeField) As AvgB
FROM TableName AS T
WHERE T.BoyGirlField = "Boy") AS AvgBoys,
(SELECT Avg(TT.GradeField) As AvgG
FROM TableName AS TT
WHERE TT.BoyGirlField = "Girl") AS AvgGirls
FROM TableName;

AvgBoys and AvgGirls are added as aliases for the subqueries themselves and
therefore become the "field" names for their respective results.
 
SELECT Avg(T.GradeField) As AvgB
FROM TableName AS T
WHERE T.BoyGirlField = "Boy";

In the above, I am using T as an alias for the table name. Although not
required when the query is run on its own, subqueries often require the use
of aliases so that Jet knows which tables go where. It's also needed in this
case because, when this is used as a subquery to the first one (top of this
post), Jet needs to know that there are two copies of TableName table being
used and they are not to be the same.
Click to expand...

OK. I don't quite get this bit. Why are there two copies of tablename being
used - aren't they the same table? Do we end with three copies? (TableName,
its alias T, and its alias TT).

Thanks for this - I'm sure that you've got better things to do!

M.
 
Yes, the full query is using three copies of the same table. Jet cannot lock
a table and then try to use it again, so it creates a buffer containing the
first copy of the table, and the same for the second copy of the table.
Thus, to Jet, there are three tables, which just all happen to contain the
same data, but they are separate and distinct.

At the point in my "monologue", I had shown just two tables so far, hence my
reference to two tables.
 
Ken Snell said:
Yes, the full query is using three copies of the same table. Jet cannot lock
a table and then try to use it again, so it creates a buffer containing the
first copy of the table, and the same for the second copy of the table.
Thus, to Jet, there are three tables, which just all happen to contain the
same data, but they are separate and distinct.
Click to expand...

Thanks. I get it now.
At the point in my "monologue", I had shown just two tables so far, hence my
reference to two tables.
Click to expand...


Yes - I followed that. You were perfectly clear.

Thanks again. Sorted now!

M.
 
Back
Top