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:
INSERT INTO MyTable (F)
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.
Best Answer
You need
UPDATE
, notINSERT
:or:
As mendosi commented, having two columns with same name in different (upper/lower) case, like
f
andF
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: