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

month over month decline query

P: n/a
I am trying to construct a query that sums sales by salespersons and
only returns a list of salespersons that have a sum of sales lower than
the previous month for whatever date rane is selected.
For example, if the user selects a date range of Sept.1 ,2004 thru Dec.
31, 2004 the query will return only salespersons where October was
worse than September and November was worse than october and December
was worse than November...

Is ths possible?

The basic query is:

SELECT Contacts.UniqueID, Contacts.LastName, Sum(Sales.Amount) AS
SumOfAmount
FROM Contacts LEFT JOIN Sales ON Contacts.UniqueID = Sales.UniqueID
WHERE (((Sales.SaleDate) Between [Enter a Starting Date:] And [Enter an
Ending Date:]))
GROUP BY Contacts.UniqueID, Contacts.LastName;

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
laurenq uantrell wrote:
I am trying to construct a query that sums sales by salespersons and
only returns a list of salespersons that have a sum of sales lower than
the previous month for whatever date rane is selected.
For example, if the user selects a date range of Sept.1 ,2004 thru Dec.
31, 2004 the query will return only salespersons where October was
worse than September and November was worse than october and December
was worse than November...

Is ths possible?


Strict descending, is that called it?
I think it is possible.

A first step may be selecting all pairs of months where one month (date)
is smaller than the other, but the sum of sales is larger. I would limit
this to the date range you supply.

The second step is finding salesperson for who all possible month pairs
occur in the result of the first step. You are interested in only those
pairs where the months differ by just one, but that is not different in
this set.

Does that make sense?

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea
Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.