SQL QUERY: Subselects and aggregiate functions

[Rob W]

Greetings,

This is a follow up to a previous question about implementing a ratings
system, is the following possible in principle (See below code)?

It errors with "Operation must use an Updateable query".

How can I set the field 'Rating' single value where I need to peform an
aggregaite function (Average), Im confused.
I've used hardcoded values for testing purposes.

Can anyone please give a help hand?

Thanks Rob

UPDATE FAQ
SET Rating = (SELECT AVG(Rating + 7) FROM FAQ WHERE Category = "Email" AND
Question = "New" GROUP BY FAQ.Category, FAQ.Question)
WHERE Category ="Email"
AND QUESTION ="Question";

 

[Douglas J. Steele]

In general, it's not recommended that you store computed results in tables.
What happens if you make changes to the table so that the computed value now
is different that what's stored?

If you really must, you can't use a SELECT statement as the target of a SET
clause: Access doesn't know that only a single value is going to be
returned. Instead, use the DAvg domain function:

UPDATE FAQ
SET Rating = DAVG("Rating + 7", "FAQ", "Category = 'Email' AND Question =
'New')
WHERE Category ="Email"
AND QUESTION ="Question";

 

[Rob W]

Thanks for the reply, apologies but what I didnt state is that i'm running
the SQL QUERY via Java Database connection to update my MS Access database.

I used the code below (added the missing " from your code) and all its doing
is adding the value each time and not calculating an average, am I right to
presume DAVG isnt supported by SQL??

UPDATE FAQ
SET Rating = DAVG("Rating + 9", "FAQ", "Category = 'Email' AND Question =
'A'")
WHERE Category ="Email"
AND QUESTION ="A";

Thanks
Rob

 

[Rob W]

I've stupidly overlooked the fact that the rating is for a single question
that belong to a category so you cant do an average for a single value!!!

I will have to capture each users rating won't I and record them in another
table?

Cheers
Rob

 

[Douglas J. Steele]

Well, the average of a single value is that value, but yes, I'd expect that
you'll want to capture each rating in another table.

And I don't believe DAvg will work if you're running the query from outside
of Access. In that case, you're strictly going through Jet, not Access, and
Jet is very limited in terms of what functions is knows about.