I am trying to fill, with a SQL query, a field F with the percentage over the total of some field f, such that each record F(i) in field F is given by:

F(i) = f(i)/SUM_i(f(i))

Both fields f and F and located in a table MyTable.

I am however struggling to develop a query which manages to do this. My try was:

SELECT f/MyField.fSum
FROM MyTable, (SELECT SUM(f) AS fSum FROM MyTable) AS MyField

But this code inserts new lines in table MyTable. I don't know how I can proceed from here, as my understanding is that the UPDATE statement is quite limited and wouldn't be useful for doing this.

Any ideas on how I could manage to do this in the simplest form possible?

I have just called the columns f and F here for illustration purposes, it is not how they are named in the actual DB.


You need UPDATE, not INSERT:

SET F = f / (SELECT SUM(f) AS fSum FROM MyTable) ;


    MyTable AS my,
    (SELECT SUM(f) AS fSum FROM MyTable) AS total
SET my.F = my.f / total.fSum ;

As mendosi commented, having two columns with same name in different (upper/lower) case, like f and F is not recommended in any DBMS and (not sure) probably not even allowed in Access.

The above statements however do not work in version 2013. I think they worked in old (1997) version but some change in the Jet engine (3.5? 4.0?) made the queries throw error instead.

Using the DSUM() function instead, to do the summation, works:

SET F = f / DSUM("f", "MyTable") ;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.