Geek Answers Handbook

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

I have a query that displays elements with a name and its weight in relation to the entire bucket of elements. These buckets are divided into categories, so I want to convert the total weight to the weight of a subcategory.

Presetting, with weight in relation to the entire bucket, is 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 that I quickly did in Excel, which I could not achieve using the query right now. Numbers are summed up to 1 at will:

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

Basically the task is to divide each entry into SUMall weights in the first table above.

I tried:

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

and Access cannot share records together. Is there a way to save the SUM (Weight) as a variable somewhere from the query and use it for each individual weight?

+4
1

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

+10

More articles:


All Articles