"join" and "sum" over 3 tables

[Joe Koenig]

Hi,

sorry if this question is a tad bit lame, but I ran out of practice re
SQL and and can't seem to solve this on my own.



Products
----------------------------------------------------
Product_ID_PK | Product_Name | Product_Price
----------------------------------------------------
1 | Shirt | $50
2 | Pants | $100
3 | Coat | $200
4 | Belt | $20
[...]



Salespersons
----------------------------------------------------
Salesperson_ID_PK | Salesperson_Name
----------------------------------------------------
1 | James Cooper
2 | Jim Brown
3 | Andrea Smith
[...]



Sales
----------------------------------------------------
Sales_ID_PK | Product_ID | Salesperson_ID | Amount
----------------------------------------------------
1 1 | 1 | 2 | 100
2 2 | 2 | 2 | 150
3 3 | 1 | 3 | 1000
4 4 | 2 | 3 | 1000
5 5 | 3 | 3 | 1100
6 6 | 4 | 3 | 500
[...]




So, I want to query the volume of sales per salesperson. The result
should look something like this:



Salesperson | Volume
-----------------------
James Cooper | $0
Jim Brown | $20000
Andrea Smith | $380000
[...]

i.e.: expr: Products.Product_Price*Sales.Amount


Thank you for your attention

Joe

P.S.: it might take me until Monday to give feedback

 

[Michel Walsh]

SELECT SalesPersonName, SUM(ProductPrice)

FROM (Sales INNER JOIN Products
ON Sales.ProductID=Products.ProductID)
INNER JOIN SalesPerson
ON Sales.SalesPersonID=SalesPerson.SalesPersonID

GROUP BY SalesPersonName



Hoping it may help,
Vanderghast, Access MVP