shadow9449
Registered User.
- Local time
- Yesterday, 20:06
- Joined
- Mar 5, 2004
- Messages
- 1,037
I'm trying to write a query for a report that will find all client who have spent more than a certain amount of money in any calendar year during the stated time interval.
The user has a screen where he/she can enter a start date, an end date and a minimum amount. The minimum amount spent would indicate the minimum that the client spent in total in any given year, not during the interval of the query.
Example 1:
User selects between Jan 1, 2005 and Dec 31, 2007, and $100 as the minimum. If a client spent $100 in 2005, this client is included even though he didn't make any purchases in 2006 or 2007
Example 2:
User selects betwee Jan 1, 2006 and Dec 31, 2008 and the same $100 minimum. If a client purchased $90 in goods every year, she will be excluded because she's never made the minimum yearly purchases.
Right now I can't see how this can be done other than using some coding with looping but I'm hoping someone can figure a way to do it using queries.
Many thanks in advance
SHADOW
The user has a screen where he/she can enter a start date, an end date and a minimum amount. The minimum amount spent would indicate the minimum that the client spent in total in any given year, not during the interval of the query.
Example 1:
User selects between Jan 1, 2005 and Dec 31, 2007, and $100 as the minimum. If a client spent $100 in 2005, this client is included even though he didn't make any purchases in 2006 or 2007
Example 2:
User selects betwee Jan 1, 2006 and Dec 31, 2008 and the same $100 minimum. If a client purchased $90 in goods every year, she will be excluded because she's never made the minimum yearly purchases.
Right now I can't see how this can be done other than using some coding with looping but I'm hoping someone can figure a way to do it using queries.
Many thanks in advance
SHADOW