Percentage of total

[BlueMonkey]

Hi,

I'm sure this is simple for someone, but I can't get my head around it:

I have a number of products that we sell, and I want my query to display actual units sold, and a percentage it's sales against all units sold.

I can't get this answer within one query (but I can in two)

Thanks in advance

B.M.

 

[lespaul]

Why don't you post the SQL of the two statements you can use to get what you need and maybe someone can modify them for you to get it all at once.


Leslie

 

[BlueMonkey]

OK:

I haven't connected the two surveys, because I'd really prefer it as self-contained.


Query 1 – calculates count of sales

SELECT Merch.Merch, Count(Merch.Merch) AS CountOfMerch
FROM Merch
GROUP BY Merch.Merch

Query 2 - totals all sales

SELECT Count(Surv.Surv) AS CountOfSurv
FROM Surv


Thanks

B.M.

 

[lespaul]

Not pretty and it will depend on your version of Access, but this may work (if you have to have it in a single query - which I'm not sure why having two separate queries like you have and then creating a third query from that data to get your final results?)

SELECT Merch.Merch, Count(Merch.Merch) AS CountOfMerch, (SELECT Count(Surv.Surv) FROM Surv) As TotalSales, ((Count(Merch.Merch)/(SELECT Count(Surv.Surv) FROM Surv)) * 100) As PercentOfSales
FROM Merch
GROUP BY Merch.Merch

Leslie

 

[BlueMonkey]

Brilliant thank-you. I had tried an embedded query as you have it, but I'd omitted the brackets.

Thanks

B.M.

 

[lespaul]

glad to help.

les

Leslie