Calculate Percentage of Total for Category

[LC]

Hi

I'm trying to calculate Percentage of Totals for certain categories
For example:

Table: Sales
===================
Product Region Amount
Apple North 50
Apple South 40
Pear North 30
Orange East 20
Orange West 60
Orange South 20

I know I can create a query to group information as follows

Query: Group
==========
Product Amount
Apple 90
Pear 30
Orange 100

How Do I move back this info to the Sales Table to build the following
result:

Table: Sales (New)
===================
Product Region Amount Total PercetOfCat
Apple North 50 90 =50/90
Apple South 40 90 =40/90
Pear North 30 30 =30/30
Orange East 20 100 =20/100
Orange West 60 100 =60/100
Orange South 20 100 =20/100

Thanks in advance,
LC

 

[John Spencer]

Save your totals query and join it back to the table

SELECT Product, Region, Amount
, QueryTotals.TotalAmount
, Amount/QueryTotals.TotalAmount as PerCentOfCat
FROM Sales INNER JOIN QueryTotals
ON Sales.Product = QueryTotals.Product

In query design view
== Add the table to the query
== Add the totals query to the query
== Join the product fields together (drag from one to the other)
== Select the fields you want to display
== Make the calculated total (percent) by entering
[Amount]/[QueryName].[TotalAmount]
into a field rectangle

Use this query as if it were a table. Do not make a new table.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[LC]

Works...Thanks John
At first I was pointing into that direction, but there were mistakes
in my Group Query and result was very strange. After correcting the
group query, it looks Ok.

 

[LC]

Hi,

The solution above works great for generating the query:

SELECT Product, Region, Amount
, QueryTotals.TotalAmount
, Amount/QueryTotals.TotalAmount as PerCentOfCat
FROM Sales INNER JOIN QueryTotals
ON Sales.Product = QueryTotals.Product

QueryTotals
===================
Product Region Amount TotalAmount PercetOfCat
Apple North 50 90 =50/90
Apple South 40 90 =40/90
Pear North 30 30 =30/30
Orange East 20 100 =20/100
Orange West 60 100 =60/100
Orange South 20 100 =20/100

How can I transfer those results to the table Sales
-After creating the fields Total and PercentOfCat

Table: Sales
===================
Product Region Amount Total PercentOfCat
Apple North 50
Apple South 40
Pear North 30
Orange East 20
Orange West 60
Orange South 20

Regards

 

[John W. Vinson]

Hi,

The solution above works great for generating the query:

SELECT Product, Region, Amount
, QueryTotals.TotalAmount
, Amount/QueryTotals.TotalAmount as PerCentOfCat
FROM Sales INNER JOIN QueryTotals
ON Sales.Product = QueryTotals.Product

QueryTotals
===================
Product Region Amount TotalAmount PercetOfCat
Apple North 50 90 =50/90
Apple South 40 90 =40/90
Pear North 30 30 =30/30
Orange East 20 100 =20/100
Orange West 60 100 =60/100
Orange South 20 100 =20/100

How can I transfer those results to the table Sales
-After creating the fields Total and PercentOfCat

Why would you want to?

Storing data which can be calculated from data already in the table wastes
disk, wastes time (almost any calculation will be very fast), and worst, risks
storing invalid data. Adding a single record will throw all your percentages
off. Just store the actual amount, and use a Query when you need to calculate
the totals and percents.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[LC]

Hi

I'm working on a project where the final output is an Income Statement
(Excel Pivot Table).
The reason for copying information from a Query into a Table is
because there are sequential calculations: one must end before the
other begins.

Creating the entire Income Statement nesting several Queries is
possible but there are problems with memory.

That's why we're trying to copy information into a table as soon as
calculations are completed.

Regards.

 

[John W. Vinson]

Hi

I'm working on a project where the final output is an Income Statement
(Excel Pivot Table).
The reason for copying information from a Query into a Table is
because there are sequential calculations: one must end before the
other begins.

Creating the entire Income Statement nesting several Queries is
possible but there are problems with memory.

That's why we're trying to copy information into a table as soon as
calculations are completed.

Regards.

In that case, you should be able to base an Append query on this calculated
query.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com