HELP!: calculating percentages in a query 1

[mekon]

hi all. this may be a long shot, but..

I have a table arranged as followed:

CUSTOMER PRODUCT PRODUCT_CATEGORY UNITS SALES
customer1 product1 category1 value value
customer1 product2 category2 value value
etc..
customer2 product1 category1 value value
customer2 product2 category2 value value
etc..

so this table shows what products customers have ordered, what category they fall in (e.g drinks, sweets etc), and the units sold/sales

what I would like is a query that calculates the total sales of each customer, broken down by category - in percentage format. i.e:

CUSTOMER %CATEGORY1_OF_TOTAL %CATEGORY2_OF_TOTAL
customer1 value value
customer2 value value
etc...

can this be done?!
thanks in advence for any help..

Mekon
-------------

 

[raskew]

Here's an example, using the Northwind database, which you may adapt to your own needs. To test it, copy the query SQL shown below into two new queries in Northwind. I've included [start date] and [end date] parameters. When prompted, enter something like 8/1/94 and 12/31/94.

Start with a totals query (query15)

PARAMETERS [start date] DateTime, [end date] DateTime;
SELECT Customers.CompanyName, Categories.CategoryName, Products.ProductName, [order details].[UnitPrice]*[Quantity] AS SalesAmt
FROM (Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID) INNER JOIN ((Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID
WHERE (((Orders.OrderDate) Between [start date] And [end date]))
GROUP BY Customers.CompanyName, Categories.CategoryName, Products.ProductName, [order details].[UnitPrice]*[Quantity];

Then, a crosstab query (Query15_crosstab) to display the amounts in each category:

TRANSFORM Sum([SalesAmt])/[Total of SalesAmt] AS [The Value]
SELECT Query15.CompanyName, Sum(Query15.SalesAmt) AS [Total Of SalesAmt]
FROM Query15
GROUP BY Query15.CompanyName
PIVOT Query15.CategoryName;

The crosstab was created using the crosstab wizard, and initially read:

TRANSFORM Sum(Query15.SalesAmt) AS [The Value] …..

To return a percentage of total sales, it was necessary to manually modify the first line to read:

TRANSFORM Sum([SalesAmt])/[Total of SalesAmt] AS [The Value]…

…and then, in design view, highlight the field, right-click to bring up the shortcut menu, select Properties and set the format to Percent.

This Northwind example use several tables to return the information, but the principal would be the same using one combined table.

 

[mekon]

many thanks for this.

I'll have a play with the queries and see how I do

M
----------