Fill percentage of total field with MS Access SQL

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You need UPDATE, not INSERT:

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

or:

UPDATE 
    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:

UPDATE MyTable
SET F = f / DSUM("f", "MyTable") ;

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply