Divide the value of each row by the SUM of this column

Question

I have a query that display items with name and its weight with respect to the entire bucket of items. These buckets are sub-categorized so I want to convert total weight to sub-category weight.

Pre-adjustment, with weight with respect to entire bucket looks as follows.

InvestmentName  Weight
Ac              0.01236194
AG              0.04102656
Ca              0.02483226
DF              0.02951128
Ea              0.003295817
GE              0.005415929

This is the desired result I quickly did in Excel, which I could not achieve with a query right now. The numbers sum to 1 as desired:

InvestmentName  AdjWeight
Ac              0.106162299
AG              0.352329321
Ca              0.213255347
DF              0.253437998
Ea              0.028303932
GE              0.046511104

Basically the task here is to divide each entry by the SUM of the all the weights in the first table above.

I tried:

SELECT InvestmentName, Weight / SUM(Weight) AS AdjWeight FROM Records WHERE = "..."

and Access said can't divide entries by aggregate. Is there a way to store the SUM(Weight) as a variable somewhere from a query and use it on each individual weight?

Answer 1

SELECT InvestmentName, Weight / (SELECT SUM(Weight) FROM Records) AS AdjWeight FROM Records;