I have a form with a field called Salseperson and another field named 
Sales.  I want to make a chart that shows the percen sales of each 
Salesperson  but  I am stuck at the query.
I make a query and get the following:

Salesperson 		Sales
John			15
Tom			35
Harry			30
Bill 			20

I am stuck at the above because I cannot figure out using the query how to 
get a third calculated column as follows:

Salesperson 		Sales   	%Sales
John			15		15
Tom			35		35
Harry			30		30
Bill 			20		20

The problem I am having is how to establish the total sales (100) and use 
this total to establish the third column.

I hope I am clear in what I want to do.  Thanks for any Help

If (for some strange reason) [Sales] always adds up to 100, then you just 
need to have the same column in twice.

If (more likely) you need to find the percentage of the sales where the 
total changes, make 2 queries - 
Query1
SELECT Sum(sales) AS TotalSales FROM Table1;


Query2
SELECT Table1.salesperson, [sales]/[totalsales]*100 AS [%Sales] FROM 
Table1, Query1;

Assuming you have this data in Table1

John      5
Dave      10
Frank     2

You'll get this as the result of Query2

John      29.4
Dave      58.8
Frank     11.8


Which is (i think) what you're after

If you need to do this often, i.e.predict that it will be requested 
for many reports, you may wish to consider Crystal Reports. It 
can make a future total available for calculations. I use it by 
choice in such situations, but I already am familiar with it.

Paul

Access can also do a percent of total calculation but it requires two passes
to create the report.  I suspect that CR also performs multiple passes
through the data to do a percent of total calculation.

Rand

-----Original Message-----
From: PStreeter@C... [mailto:PStreeter@C...]
Sent: Thursday, January 30, 2003 10:09
To: Access
Subject: [access] Re: calculate percent of total in query to use in graph

If you need to do this often, i.e.predict that it will be requested
for many reports, you may wish to consider Crystal Reports. It
can make a future total available for calculations. I use it by
choice in such situations, but I already am familiar with it.

Paul

Steven,

That method looks good, with one caveat.  You may need to create a temporary
table to "FREEZE" the data.  Then do your two queries against the temporary
table. Otherwise if someone changes the data in Table1 while query1 is
running then query2 will give incorrect results.

Query0
SELECT * INTO TempTable FROM Table1;

Query1
SELECT Sum(sales) AS TotalSales FROM TempTable;
 
Query2
SELECT TempTable.salesperson, [sales]/[totalsales]*100 AS [%Sales] FROM
TempTable, Query1;

Rand

-----Original Message-----
From: Steven White [mailto:Steve.White@m...]
Sent: Thursday, January 30, 2003 06:04
To: Access
Subject: [access] Re: calculate percent of total in query to use in graph

If (for some strange reason) [Sales] always adds up to 100, then you just
need to have the same column in twice.

If (more likely) you need to find the percentage of the sales where the
total changes, make 2 queries -
Query1
SELECT Sum(sales) AS TotalSales FROM Table1;


Query2
SELECT Table1.salesperson, [sales]/[totalsales]*100 AS [%Sales] FROM
Table1, Query1;

Assuming you have this data in Table1

John      5
Dave      10
Frank     2

You'll get this as the result of Query2

John      29.4
Dave      58.8
Frank     11.8


Which is (i think) what you're after

On Thu, 30 Jan 2003 10:25:56 -0600 "Gerald, Rand" wrote:

> Access can also do a percent of total calculation but it requires two 
> passes
> to create the report.  I suspect that CR also performs multiple passes
> through the data to do a percent of total calculation.
> 
exactly

PGS

>That method looks good, with one caveat.  You may need to create a 
>temporary table to "FREEZE" the data.  Then do your two queries against 
>the temporary table. Otherwise if someone changes the data in Table1 ?
>while query1 is running then query2 will give incorrect results.


I doubt that this would be necessary, since query2 is querying a query - 
not a static recordset. It's only separated into 2 queries to make it 
easier to write. 
Besides that, Access locks the data anyway.
The only time it MAY be possible for this to fail, and require a 
temporary table, would be if - between the nanosecond it takes your 
computer to do an addition and a division, numbers got changed - but like 
I said, it's only a nanosecond, and HIGHLY unlikely

Steven: 

Where does the totalsales in the expression 
     ... sales]/[totalsales]*100 AS [%Sales] FROM Table1, Query1;

comes from ?

First I would have to sum all individual sales but I cannot figure that in 
the query

Steven: 

Where does the totalsales in the expression 
     ... sales]/[totalsales]*100 AS [%Sales] FROM Table1, Query1;

comes from ?

First I would have to sum all individual sales and that is where I am 
having the problem.