Fill percentage of total field conditional on some other field with MS Access SQL
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...