E
Emlou85 via AccessMonster.com
I have created a query (QryAverage) based on a table. The query contains all
the fields contained in the table (AdNo, Forename, Surname, Test Result 1,
Test Result 2, Test Result 3, Test Result 4, Test Result 5, Test Result 6).
The query then contains 3 calculated fields - Total (adds up the test result
scores) Average (takes the total and divides by 6) and a rounding expression
(which rounds the average score).
SQL for this query is as follows:
SELECT [tblTest Results].AdNo, [tblTest Results].Forename, [tblTest Results].
Surname, [tblTest Results].Test1, [tblTest Results].Test2, [tblTest Results].
Test3, [tblTest Results].Test4, [tblTest Results].Test5, [tblTest Results].
Test6, [tblTest Results]!Test1+[tblTest Results]!Test2+[tblTest Results]!
Test3+[tblTest Results]!Test4+[tblTest Results]!Test5+[tblTest Results]!Test6
AS Total, [Total]/6 AS Average, CInt([Average]) AS Rounded
FROM [tblTest Results];
This query works fine.
I have then created another query (QryGrade), which uses the above query (Qry
Average and another table (tblGradeLookup). Where it displays the
tables/queries to be used I have a created a link between the rounded field
(in Qry Average) and the 'Mark' field (in tblGradeLookup). This query uses
all the fields from QryAverage and the 'Grade' field from tblLookup.
The SQL for this query is as follows:
SELECT QryAverage.AdNo, QryAverage.Surname, QryAverage.Forename, QryAverage.
Test1, QryAverage.Test2, QryAverage.Test3, QryAverage.Test4, QryAverage.Test5,
QryAverage.Test6, QryAverage.Total, QryAverage.Average, QryAverage.Rounded,
GradeLookup.Grade
FROM GradeLookup INNER JOIN QryAverage ON GradeLookup.Mark = QryAverage.
Rounded;
This query works too, as it works out the average score, rounds it up/down
and brings back the appropriate grade (based on the rounded score). However,
my problem is that I cannot edit the marks that have been entered. If I
remove the grade field from the query I can then edit the marks but obviously
it does not show me the grade, which is essential. Can anyone help???
Thank you in anticipation!
Emma
the fields contained in the table (AdNo, Forename, Surname, Test Result 1,
Test Result 2, Test Result 3, Test Result 4, Test Result 5, Test Result 6).
The query then contains 3 calculated fields - Total (adds up the test result
scores) Average (takes the total and divides by 6) and a rounding expression
(which rounds the average score).
SQL for this query is as follows:
SELECT [tblTest Results].AdNo, [tblTest Results].Forename, [tblTest Results].
Surname, [tblTest Results].Test1, [tblTest Results].Test2, [tblTest Results].
Test3, [tblTest Results].Test4, [tblTest Results].Test5, [tblTest Results].
Test6, [tblTest Results]!Test1+[tblTest Results]!Test2+[tblTest Results]!
Test3+[tblTest Results]!Test4+[tblTest Results]!Test5+[tblTest Results]!Test6
AS Total, [Total]/6 AS Average, CInt([Average]) AS Rounded
FROM [tblTest Results];
This query works fine.
I have then created another query (QryGrade), which uses the above query (Qry
Average and another table (tblGradeLookup). Where it displays the
tables/queries to be used I have a created a link between the rounded field
(in Qry Average) and the 'Mark' field (in tblGradeLookup). This query uses
all the fields from QryAverage and the 'Grade' field from tblLookup.
The SQL for this query is as follows:
SELECT QryAverage.AdNo, QryAverage.Surname, QryAverage.Forename, QryAverage.
Test1, QryAverage.Test2, QryAverage.Test3, QryAverage.Test4, QryAverage.Test5,
QryAverage.Test6, QryAverage.Total, QryAverage.Average, QryAverage.Rounded,
GradeLookup.Grade
FROM GradeLookup INNER JOIN QryAverage ON GradeLookup.Mark = QryAverage.
Rounded;
This query works too, as it works out the average score, rounds it up/down
and brings back the appropriate grade (based on the rounded score). However,
my problem is that I cannot edit the marks that have been entered. If I
remove the grade field from the query I can then edit the marks but obviously
it does not show me the grade, which is essential. Can anyone help???
Thank you in anticipation!
Emma