Query Update Error

[Eoin09]

Hey Guys...

I'm having a bit of a problem.
I'm designing an exam system for a college using visual studio 2010, my code is written in .aspx and my database is access 2010.

Basically I have a table called Assignment_Result,
here I store the students unique id, the modules the students is enrolled in, the course he/she is enrolled in, the academic year that the course is happening in, an assignment ID (where duplicates are allowed, as more than one student may be taking a module) and a grade assigned to a module assignment.

Then I have a query with three field (student ID, student GPA and Overall Result) The query calculates the students average grade for all his / her modules where the student ID is the same, but in that query I want the the field OverallResult to update to some thing like '1.1H' WHERE (GPA >= 70) Is there a way of doing this from Access or will I have to do this from the code? I have tried both ways, I simply can't figure out how to do it in Access and when I try update OverallResult in the code it says some thing like Can't update field, but My syntax is correct.

I'm wondering is the reason it isn't working is because Access won't allow me to run an update statement while a calculation (to get the students average result for all modules) is running.

Can anyone help me out? Offer me any solution?

*All help appreciated

 

[Galaxiom]

Queries with aggregate components are not updateable.

You should not be storing the calculated value anyway but deriving it as required for the report. Storing it breaches normalisation.

 

[gblack]

I guess normalization is god and may very well smite me down, because I just posted a solution to the very same type of issue a few minutes ago in a thread entitled “Updated query with Select statement”.

I know for a fact people on this forum are WAY-WAY smarter than I am and usually get me the answers I am looking for. But IMO, sometimes... when dealing with data... you just want the aggregated/calculated information for other purposes… maybe to be used with other tables found in other database files.

MS Access isn't always used as a singular normalized database application. I guess in this case it might be... but sometimes it’s used as workshop with a bunch of handy tools for slicing and dicing data. I’ve been pulling data into Access (from all sorts of formats) for many years… maybe some of the stuff I do isn’t kosher… I am sure people might say i am using it wrong. But if it's getting me what I need and helps me get my job done... I don't really care if someone else doesn't like the way I use it. That’s really the bottom line imho.

Understanding the practice of housing calculated values is a no-no in regards to normalization, I’d rather people told me this… then told me how to do what I want to do… and let ME decide if sitting under the normalization sword of Damocles, is worth getting the job done.

That said, one way to get around your issue is to use a make table query (i.e., Select your, aggregated, data,…Into SomeNewTable).

Once you have your aggregated data in a table format, you can deal with it however you like. (see this thread: http://www.access-programmers.co.uk/forums/showthread.php?t=219805for ideas on how to get around the fact that your update query doesn’t like you .

 

[Galaxiom]

There are cases where the storage of calculated values makes sense. For example, if the calculation itself is very time consuming.

Sure it is up to the individual developer to make that decision but that decision often leads to complexities such as the one in the original post. It can also lead to errors in the most accessible data when the underlying data is edited unless procedures are included to update the result. It often leads to considerably more work and considerably less reliability of the data.

The most common workaround is to create temporary tables. If you do you this technique then please remember to write those tables to a temporary database (a side end), not the front end or back end where they will cause bloating.