Newbie: Cross Tab. With Percentages ?

  • Thread starter Thread starter richmarin
  • Start date Start date
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 ???

...
 
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
 
Thanks for the interest.

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