Ms-access – Fill percentage of total field conditional on some other field with MS Access SQL

ms accessms-access-2013

This question is an extension of Fill percentage of total field with MS Access SQL.

I have the following 2013 MS Access SQL query:

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

This query fills field F with the percentage over the total of some field f, such that each one of the records F(i) in field F is given by:

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

Both fields f and F belong to table MyTable.

Now, I would like, instead of simply computing the percentage over the whole table, to do it by Date, where Date is a 3rd field also to be found in table MyTable. I will make up an example for illustration purposes. Let's assume a simplified MyTable with 2 records per date; then that's what I would like MyTable to look like after executing my desired query… – see fields f and F:

|    Date    | Department |     f     |     F     |
|------------|------------|-----------|-----------|
|     t1     |     D1     |     2     |    40%    |
|     t1     |     D2     |     3     |    60%    |
|     t2     |     D1     |     1     |    20%    |
|     t2     |     D2     |     4     |    80%    |

… And this is how it is looking right now, after executing the query displayed above:

|    Date    | Department |     f     |     F     |
|------------|------------|-----------|-----------|
|     t1     |     D1     |     2     |    20%    |
|     t1     |     D2     |     3     |    30%    |
|     t2     |     D1     |     1     |    10%    |
|     t2     |     D2     |     4     |    40%    |

I have tried the following code, but none yielded what I wanted.

Try #1:

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

Try #2:

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

Any ideas on how I could manage to do this?

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

I will add here further tests that haven't worked out:

Try #3:

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

Try #4: this has worked but only for a single Date, the 1st one to be recorded – i.e. the one in the 1st row/record of the table MyTable – for the rest of dates the value for F is empty.

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

Best Answer

Your last UPDATE statement should work. Access is often a bit crap though so although it should work, it probably won't. Thanks Microsoft Access dev team (probably more specifically the updates to JET).

Unfortunately, the only way I can recommend is to create a temp table based on a query which does a GROUP BY on the Date column you are attempting to aggregate. So create a query first like this:

SELECT MyTable.Date, Sum(MyTable.f) AS SumOfVal INTO temp_DateSums
FROM MyTable
GROUP BY MyTable.Date;

Then create another query which joins to this new table and performs its update based on it. This second query would look like:

UPDATE MyTable 
INNER JOIN temp_DateSums ON MyTable.Date = temp_DateSums.DateDate 
SET MyTable.F = [MyTable].[f]/[temp_DateSums].[SumOfVal];

This should update the column you need with the right value, albeit running 2 queries instead of one. I can't think of another way around it running a single query where you wouldn't get the dreaded "Operation must use an updatable query" option, that doesn't use VBA. Again, your DSUM should work, but DSUM doesn't seem to want to work the way it was designed anymore...

Related Question