Query giving sales volume and sales SHARE

[Jokkis]

I’m trying to create a query in Access that’ll give me a list of items with sales volume and sales share (market share).

To illustrate my challenge we can use a very simplified table that consists of PRODUCT and SALES VOLUME.

Example Table:
PRODUCT SALES VOLUME
Product A 40
Product B 25
Product C 20
Product D 50
Product E 35
Product F 30

It’s easy of course to get the sales volume, but I’m not sure how to create a query that’ll also list the product’s sales share. I’m not even sure if it’s possible with SQL/Access to do so. For example, I would like to list PRODUCT A and PRODUCT C with SALES VOLUME and a third field presenting the sales share for each of those two products. The result I'm trying to creat would be:
PRODUCT SALES VOLUME SALES SHARE
Product A 40 20%
Product C 20 10%

Can you please help me to create a query that’ll give me such a result?

I’m extremely thankful for all help – thanks!

Jokkis

 

[lespaul]

create two queries:

qryAllSales
SELECT SUM(SalesVolume) As AllSales FROM TableName

qryProductSales
SELECT Product, SUM(SalesVolume) As TotalSales, SUM(SalesVolumne)/qryAllSales.AllSales As MarketShare FROM TableName
GROUP BY Product

That should get you started.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[randy700]

How about..

SELECT Product, [Sales Volume], [Sales Volume]/Sum([Sales Volume]) AS [Sales Share]
FROM [COLOR=red]YourTableName[/color]
GROUP BY Product

Randy

 

[Jokkis]

Many thanks for your input.

A problem now is that Access constantly gives me an error message when I try to use data from another query (as you suggested lespaul) and the same if I follow your suggestion Randy:

"You tried to execute a query that does not include the specified expression 'SUM(TableName.SalesVolume)/qryAllSales.SumOfV' as part of an aggregation function."

I've set the total criteria for the MarketShare as "Expression" in Access, but obviously Access doesn't allow this as long as I try to use a result from a sub-query or from another query. Just for testing I tried to "hardcode" the value for the total sales volume (which is the number I need to divide on), and then it works fine...

Any suggestions

 

[Golom]

Try this

SELECT Product
     , SUM(SalesVolume) As TotalSales
     , (SUM(SalesVolume)/
       (SELECT SUM(SalesVolume) FROM TableName))
       As MarketShare 

FROM TableName
GROUP BY Product

 

[Jokkis]

BIG THANKS Golom! That solved my problem. Must admit that my SQL knowledge is rather rusty...

Many thanks to you all for your valuable help!