Using 2 fields in report function

[Guest]

I use an Access 2000 database to track test scores. I’m using different
fields to track these by weight, named Majortst, Weeklytst, and Quiz. I also
have been tracking when these were given in another field I call Timeadm -
this field is completed through a table, with choices like Early, Midmorning,
Prelunch, Postlunch etc.

I would like to set up a Report function to show the averages of these
different test types by time administered, i.e. the average score for a
Weekly test given Midmorning, another showing the average of the same test
given postlunch.

I’m NOT real swift re databases, but if someone can show me how to set up
one function I can substitute fields to get the other answers I’m looking for
(I hope!). Thanks in advance if you can help!

 

[Marshall Barton]

I use an Access 2000 database to track test scores. I’m using different
fields to track these by weight, named Majortst, Weeklytst, and Quiz. I also
have been tracking when these were given in another field I call Timeadm -
this field is completed through a table, with choices like Early, Midmorning,
Prelunch, Postlunch etc.

I would like to set up a Report function to show the averages of these
different test types by time administered, i.e. the average score for a
Weekly test given Midmorning, another showing the average of the same test
given postlunch.

I’m NOT real swift re databases, but if someone can show me how to set up
one function I can substitute fields to get the other answers I’m looking for
(I hope!).

If by function, you mean a VBA Function procedure, it would
be rather messy because aggregate operations are best done
in a report's record source query.

It may be the best route for you to create a report that
displays just the summary data obtained from a Totals type
query something like

SELECT Timeadm, Avg(Score) As Average
FROM thetable
GROUP BY Timeadm

A quick and dirty answer to your question is to use text
box's in the report footer section with expressions like:

=Avg(IIf(Timeadm = "Midmorning", Score, Null))

 

[Duane Hookom]

How about SQL like:
SELECT TimeAdm, Avg([TestScore]) as TimeAverage
FROM tblTestScores
GROUP BY TimeAdm;

 

[Guest]

Thanks so much both of you. Right now Im going with the exression Masrhal
gave me, but now I have lots more to work with!
Thanks again,
Gary