By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,400 Members | 953 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,400 IT Pros & Developers. It's quick & easy.

The Sum of data, Dependant on Dates of the month

P: 14
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.
Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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
Nov 29 '06 #1
Share this Question
Share on Google+
3 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...

Expand|Select|Wrap|Line Numbers
  1. SELECT Sales_Staff_No, SUM(Purchased_Price) 
  2. FROM CarSales 
  3. WHERE Month(Date_Of_Purchase) = IIf(Month(Now()) = 1, 12, Month(Now()) - 1)
  4. GROUP BY Sales_Staff_No;
  5.  
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
Nov 30 '06 #2

P: 14
owz
Thanx work perfect.
u where a great help.
Nov 30 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanx work perfect.
u where a great help.
You're welcome.

Mary
Nov 30 '06 #4

Post your reply

Sign in to post your reply or Sign up for a free account.