Averages for Multiple Types

[bswood7]

Hello,

I have a database of test scores, where I need to figure out averages.
However, the test is administered in several ways - computer, paper,
terminals... I need to figure out how to create a query (one rather than
three separate) that will allow me to figure out the averages based upon the
test type. I have figured out all the rest of the averages for my current
query, I just cannot get this part right. See below for examples!

So my data looks like:

Score Test Type
150 Paper
102 Paper
187 Paper
605 Computer
450 Computer
320 Computer
270 Terminals
268 Terminals

 

[Michel Walsh]

SELECT TestType, AVG(score)
FROM tableNameHere
GROUP BY TestType




or, in the query designer, bring your table. Click on the summation button,
the one with the capital Sigma, a rotated M, such that a new line, total,
appear in the grid. Drag the field TestType, keep the proposed Group By.
Bring the field score in the grid, now, change its Group By to Avg
(average). That's all.



Hoping it may help,
Vanderghast, Access MVP

 

[raskew via AccessMonster.com]

Hi -

Here's an example that returns average salary by city. Try substituting your
table and field names:

SELECT
Table1.City
, Avg(Table1.Salary) AS AvgOfSalary
FROM
Table1
GROUP BY
Table1.City;

HTH - Bob

 

[KARL DEWEY]

Is this what you are looking for?
SELECT bswood7.[Test Type], Avg(bswood7.Score) AS AvgOfScore
FROM bswood7
GROUP BY bswood7.[Test Type];

If not then post an example of what you want for results from the sample data.

 

[bswood7]

I think in my effort to simplify my request, I have become more confused.
This is what I have right now...

SELECT DISTINCTROW Avg([AppTestScores].GREQuant) AS [Avg Of GREQuant],
Avg([AppTestScores].GREVerbal) AS [Avg Of GREVerbal],
Avg([AppTestScores].GREAnalytical) AS [Avg Of GREAnalytical], Count(*) AS
[Count Of 2008-09 App Status]
FROM [Application Status and Decision] INNER JOIN [AppTestScores] ON
[Application Status and Decision].UniversityID = [Applicant Test Scores &
Education History].UniversityID
HAVING ((([AppTestScores].StudentStatus)="international") AND (([Application
Status and Decision].Notes) Not Like "*incomplete*") AND (([Application
Status and Decision].TermCode)=1054));

I want to add three more fields to this query. One field which will
calculate the average for the Paper scores, one which will calculate the
average for the Computer based test, etc.

Where do I fit this in and how? Or is it not possible?

Is this what you are looking for?
SELECT bswood7.[Test Type], Avg(bswood7.Score) AS AvgOfScore
FROM bswood7
GROUP BY bswood7.[Test Type];

If not then post an example of what you want for results from the sample data.

--
KARL DEWEY
Build a little - Test a little

Hello,

I have a database of test scores, where I need to figure out averages.
However, the test is administered in several ways - computer, paper,
terminals... I need to figure out how to create a query (one rather than
three separate) that will allow me to figure out the averages based upon the
test type. I have figured out all the rest of the averages for my current
query, I just cannot get this part right. See below for examples!

So my data looks like:

Score Test Type
150 Paper
102 Paper
187 Paper
605 Computer
450 Computer
320 Computer
270 Terminals
268 Terminals

 

[KARL DEWEY]

Huh? What fields have [Test Type] and Score?
--
KARL DEWEY
Build a little - Test a little

I think in my effort to simplify my request, I have become more confused.
This is what I have right now...

SELECT DISTINCTROW Avg([AppTestScores].GREQuant) AS [Avg Of GREQuant],
Avg([AppTestScores].GREVerbal) AS [Avg Of GREVerbal],
Avg([AppTestScores].GREAnalytical) AS [Avg Of GREAnalytical], Count(*) AS
[Count Of 2008-09 App Status]
FROM [Application Status and Decision] INNER JOIN [AppTestScores] ON
[Application Status and Decision].UniversityID = [Applicant Test Scores &
Education History].UniversityID
HAVING ((([AppTestScores].StudentStatus)="international") AND (([Application
Status and Decision].Notes) Not Like "*incomplete*") AND (([Application
Status and Decision].TermCode)=1054));

I want to add three more fields to this query. One field which will
calculate the average for the Paper scores, one which will calculate the
average for the Computer based test, etc.

Where do I fit this in and how? Or is it not possible?

Is this what you are looking for?
SELECT bswood7.[Test Type], Avg(bswood7.Score) AS AvgOfScore
FROM bswood7
GROUP BY bswood7.[Test Type];

If not then post an example of what you want for results from the sample data.

--
KARL DEWEY
Build a little - Test a little

Hello,

I have a database of test scores, where I need to figure out averages.
However, the test is administered in several ways - computer, paper,
terminals... I need to figure out how to create a query (one rather than
three separate) that will allow me to figure out the averages based upon the
test type. I have figured out all the rest of the averages for my current
query, I just cannot get this part right. See below for examples!

So my data looks like:

Score Test Type
150 Paper
102 Paper
187 Paper
605 Computer
450 Computer
320 Computer
270 Terminals
268 Terminals