Help a new Access user summarize data

[afabris]

Hi all.

I am failing at setting up a query to summarize data.

I have a table that contains the amount of items sold per salesman per month (key + 5 fields: month, year, salesman, item, amount sold).


In a second table, I have the average price per item per month (key + 4 fields: month, year, item, price).

I am trying to run a query that shows total sales (amount sold * price) per salesman per year, but I can't do it. I get sum of amounts or sum of prices, I cannot sum the multplication of both.

Thanks for your time and help!

[orange]

Please show us your relationships window as a zipped jpg file.

[Dal Jeanis]

SELECT TS.Salesyear, TS.Salesman, SUM(TS.Item * TS.Amount * TP.Price)
FROM tblSales AS TS INNER JOIN tblPrice AS TP
ON TS.SalesYear = TP.PriceYear 
AND TS.SalesMonth = TP.PriceMonth
AND TS.Item = TP.Item
GROUP BY TS.Salesyear, TS.Salesman;

Of course, you'll have to change all the table names and field names to match yours.

I hope you are not using "Month" and "Year" as field names. they are reserved words, and will cause you loads of headaches in the long run.

[afabris]

SELECT TS.Salesyear, TS.Salesman, SUM(TS.Item * TS.Amount * TP.Price)
FROM tblSales AS TS INNER JOIN tblPrice AS TP
ON TS.SalesYear = TP.PriceYear 
AND TS.SalesMonth = TP.PriceMonth
AND TS.Item = TP.Item
GROUP BY TS.Salesyear, TS.Salesman;

Of course, you'll have to change all the table names and field names to match yours.

I hope you are not using "Month" and "Year" as field names. they are reserved words, and will cause you loads of headaches in the long run.

That did it, thank you very much!!