Newbie: Cross Tab. With Percentages ?

R

richmarin

I want to show a percentage along side the number in a row. So customer
A buys 70 dollars worth of item A out of a total of 100 dollars worth
of purchases. That is 70% of dollars spent is on item A. Picture of
cross tab below :

Item A Precentage of A Total Purchases
Customer A $70 70% $ 100



Is the above possible with a cross tab ???

...
 
T

Tom Ellison

Dear Rich:

I assume your Total Purchases would require an aggregate, something like:

SELECT Customer, SUM(Purchase)
FROM YourTable
GROUP BY Customer

You can put this into your query as a subquery, correlated on the current
Customer:

SELECT Customer, Purchase,
(SELECT SUM(Purchase)
FROM YourTable T1
WHERE T1.Customer = T.Customer)
AS TotalPurchases
FROM YourTable T
ORDER BY Customer

Adding the percentage:

SELECT Customer, Purchase,
Purchase / (SELECT SUM(Purchase)
FROM YourTable T1
WHERE T1.Customer = T.Customer)
* 100 AS Percentage,
(SELECT SUM(Purchase)
FROM YourTable T1
WHERE T1.Customer = T.Customer)
AS TotalPurchase
FROM YourTable T
ORDER BY Customer

You don't give a lot of explanation of the circumstances, so I have assumed
they are very simple. I have no way of knowing whether that's the case or
not. If not, then I believe you will need to explain things quite
thoroughly to get an answer to meet those specifics.

Tom Ellison
 
D

Duane Hookom

Is there a reason why you would not just calculate the percentage in a
report?
 
R

richmarin

Thanks for the interest.

I am sending the output to an Excel spreadsheet via OLE. So, I will not
be using Access Reports.
 
Top