Expressing percentage

Question

I need to be able to divide the values of each record in a certain field contained in a query result by the sum of all the values in the same field. This will enable me to calculate the percentage that each record is of the whole. The results must be expressed in the same domain in a new field.

That is: Let's say in the results of QueryX - which is the table I am working with -  each record in FieldA must be divided by the sum of FieldA, then expressed as a fraction/percentage in a new, neigbouring ColumnB.

I have tried this in QueryX:

Total: (select sum(ColumnA) from QueryX) - then in a second subquery Percentage:([CountOfColumnA/Total])

This worked once, but Access stopped me with a circular error because of the QueryX from QueryX.

Now I am trying to rewrite it and reach the answer in only one subquery to avoid the circular problem. I can easily do this in Excel, but don't know enough about expressions and coding to manage it in Access.

Can anybody help?

Wilhelm

Answer 1

Hi,

You could try either of the following:

SELECT FieldA, (SELECT Sum(T1.FieldA) FROM TableName AS T1) AS Total, FieldA/Total AS Ratio FROM TableName

Or:

SELECT FieldA, DSum("FieldA", "TableName") AS Total, FieldA/Total AS Ratio FROM TableName

Hope it helps...

Answer 2

You need to divide the amount in each row of the query by the sum of the amounts, returned by a subquery, multiplied by 100.  To do this per group of rows the subquery is correlated with the outer query on whatever column or columns are the basis of the grouping.  The following is an example using Northwind, which returns each customer's expenditure per item per order, along with that expenditure as a percentage of the customers total expenditure:

SELECT Company, [Order Date], [Product Name],
[Quantity]*[Unit Price] AS Amount,
[Quantity]*[Unit Price]/
     (SELECT SUM([Quantity]*[Unit Price])
      FROM Orders As O2 INNER JOIN [Order Details]
      ON O2.[Order ID] = [Order Details].[Order ID]
      WHERE O2.[Customer ID] = O1.[Customer ID]) * 100 As Percentage
FROM Products INNER JOIN (Customers
INNER JOIN (Orders AS O1 INNER JOIN [Order Details]
ON O1.[Order ID] = [Order Details].[Order ID])
ON Customers.ID = O1.[Customer ID])
ON Products.ID = [Order Details].[Product ID]
ORDER BY Company, [Order Date];

---

Ken Sheridan, Stafford, England

Answer 3

Thank you, that was very helpful. 

I ended up with this in the design mode table:

Percentage: [FieldA]/DSum("FieldA", "TableName")

Works like a charm!

Answer 4

Thank you, that was very helpful. 

I ended up with this in the design mode table:

Percentage: [FieldA]/DSum("FieldA", "TableName")

Works like a charm!

Congratulations! Glad to hear you got it to work. Good luck with your project.

Answer 5

Hi WilhelmG,

from your last post, I can see that your issue is solved now.

but you did not mark the answer.

so this thread is still open.

I suggest you to mark the suggestion as an answer which helped you to solve your issue.

so that we can close this thread.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].