Problem editing a query based on another query and table

[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

 

[Michel Walsh]

Hi,


definitively,

ON GradeLookup.Mark = QryAverage.Rounded


implies a computed field, which stops the upgradeability since Jet cannot
track which UNIQUE row from [tblTest Results] contributes to a given
QryAverage.Rounded value. In fact, are you sure there is just one row in
[tblTest Results] that can supply a given QryAverage.Rounded value? if there
are 2 different rows, for illustration, which one have to be updated?

Now, if you can make the first query a temporary table, in fact, I think you
just need AdNo and Rounded fields from it in the temp table, then, use that
temp table and the initial table (join on AdNo) and the GradeLookup table
(join on Mark and Rounded) would bring "some" updateability, while still
displaying the information... am I right?



Hoping it may help,
Vanderghast, Access MVP

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

 

[Emlou85 via AccessMonster.com]

Hi

Thanks for your help! I have tried creating a table from the Query (through
a make table query - is there another way of making a temporary table?). It
works to some extent in that now in the query based on the 3 tables, as you
suggested, I can change the marks. However, it does not update the
calculated fields (although if I change the 'rounded' data it does change the
grade!) When I run the Make-table query then all the answers obviously are
recalculated and so the answers are right again, until I make an alteration
to one of the marks. I hope that all makes sense! Ideally I would prefer
not to have to run the make-table query each time I change/add a mark. Is
there any way around this problem?

Thanks again
Emma

Hi,

definitively,

ON GradeLookup.Mark = QryAverage.Rounded

implies a computed field, which stops the upgradeability since Jet cannot
track which UNIQUE row from [tblTest Results] contributes to a given
QryAverage.Rounded value. In fact, are you sure there is just one row in
[tblTest Results] that can supply a given QryAverage.Rounded value? if there
are 2 different rows, for illustration, which one have to be updated?

Now, if you can make the first query a temporary table, in fact, I think you
just need AdNo and Rounded fields from it in the temp table, then, use that
temp table and the initial table (join on AdNo) and the GradeLookup table
(join on Mark and Rounded) would bring "some" updateability, while still
displaying the information... am I right?

Hoping it may help,
Vanderghast, Access MVP

I have created a query (QryAverage) based on a table. The query contains
all

[quoted text clipped - 54 lines]

Thank you in anticipation!
Emma

 

[Michel Walsh]

Hi,


I really doubt there is another way. Say "the sum is 45" but made of 3
initial rows. If I change the "sum" to 46, which row, of the 3 rows, does I
change? So, even if, "by accident" there is just a single row that
contributes to the "computed value", SQL is not wired to "trace back" that
single row. It would also be a lost of time, in general, to try to keep that
back tracking, since in general, more than one single row can produce a
given same computed result.

You can re-use an existing table instead of creating it each time
(DELETE * FROM table). You can also make a table though DAO, but that is
really much harder than a SELECT INTO statement that creates a table (and
that SQL statement is independent of DAO, or ADO).

If you use a FORM, to see and change your data, you can make VBA
re-built the whole system automatically, elegantly, behind the scene. That
assumes you like to use the VBA approach.



Hoping it may help,
Vanderghast, Access MVP

Hi

Thanks for your help! I have tried creating a table from the Query
(through
a make table query - is there another way of making a temporary table?).
It
works to some extent in that now in the query based on the 3 tables, as
you
suggested, I can change the marks. However, it does not update the
calculated fields (although if I change the 'rounded' data it does change
the
grade!) When I run the Make-table query then all the answers obviously
are
recalculated and so the answers are right again, until I make an
alteration
to one of the marks. I hope that all makes sense! Ideally I would prefer
not to have to run the make-table query each time I change/add a mark. Is
there any way around this problem?

Thanks again
Emma

Hi,

definitively,

ON GradeLookup.Mark = QryAverage.Rounded

implies a computed field, which stops the upgradeability since Jet cannot
track which UNIQUE row from [tblTest Results] contributes to a given
QryAverage.Rounded value. In fact, are you sure there is just one row in
[tblTest Results] that can supply a given QryAverage.Rounded value? if
there
are 2 different rows, for illustration, which one have to be updated?

Now, if you can make the first query a temporary table, in fact, I think
you
just need AdNo and Rounded fields from it in the temp table, then, use
that
temp table and the initial table (join on AdNo) and the GradeLookup table
(join on Mark and Rounded) would bring "some" updateability, while still
displaying the information... am I right?

Hoping it may help,
Vanderghast, Access MVP

I have created a query (QryAverage) based on a table. The query contains
all

[quoted text clipped - 54 lines]

Thank you in anticipation!
Emma