The Sum of data, Dependant on Dates of the month

[owz]

Working with ACCESS 2000 SQL, I’m getting very frustrated with it now so
Any ideas would be well appreciated

I want the sum of the sales of a Salesperson for a specific month i.e. the month before.

Last month:
i.e. Salesperson ££SOLD
1 £800
2 £350


and so fourth for all my sales staff.

SELECT DISTINCT CarSales.Sales_Staff_No, (SELECT SUM(CS.Purchased_Price) FROM CarSales AS CS WHERE CS.Sales_Staff_No = CarSales.Sales_Staff_No) AS Sum_Of_Sales, CarSales.Date_Of_Purchase
FROM CarSales;

All that happens is that it does the sum off all data in the table instead of only the the data relevant 2 the Month...

How do I get the SUM 2 be dependant on the dates of a month.
I use the >=1/10/2006 AND <=31/10/2006. But that does not work, still gives the sum of all data .

Please give sum comments this is doing my head in now

[MMcCarthy]

Try this ...

SELECT Sales_Staff_No, SUM(Purchased_Price) 
FROM CarSales 
WHERE Month(Date_Of_Purchase) = IIf(Month(Now()) = 1, 12, Month(Now()) - 1)
GROUP BY Sales_Staff_No;
 

The Month function will get the month number from any date. The IIf statement will return 12 for December if the month is January otherwise it will return the current month number -1.

This kind of function is known as an aggregate function. i.e. where sum or count type functions are used. You have to group by any fields not aggregated.

Any questions, please let me know.

Mary

[owz]

Thanx work perfect.
u where a great help.

[MMcCarthy]

Thanx work perfect.
u where a great help.

You're welcome.

Mary