TIA ... really need help ...

[Jim]

The table looks like this:

EMPLOYEE $$$_SALES DATE
JOE 100 1-1-06
MARY 500 1-1-06
JANE 200 1-1-06
BOB 600 1-1-06
JOE 250 1-2-06
MARY 200 1-2-06
JANE 700 1-2-06
BOB 900 1-2-06
JOE 100 1-3-06
MARY 500 1-3-06
JANE 200 1-3-06
BOB 600 1-3-06
and so on ...


On each and every day, from 1-1-06 and forward, I need to have a
query(ies) that tracks employees who have sold at least $25,000 in the
last 90 days, and then award a 7 percent bonus to those meeting this
criteria ...

AND, if the awardee above has been an awardee in the last 180 days,
then the bonus award becomes 12 percent,

Not sure how to build the logic ... I use the design grid to create
queries so I may need a little bit of handholding if the solution is
in SQL or modules ...
Again, Much Appreciated ...

jim

 

[Allen Browne]

Use a subquery to sum the previous 90 days for the employee.

Type an expression like this into the Field row in query design:
BonusDue: (SELECT Sum([$$$_Sales) AS HowMuch
FROM Table1 AS Dupe
WHERE Dupe.Employee = Table1.Employee
AND Dupe.[Date] Between
Table1.Date - 90 And Table.Date) > 25000

That should give you -1 (i.e. True) where the person's sales exceed $25k for
the quarter, and 0 (false) where it doesn't.

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

Once you get that working, you can save the query, and then query that to
see how many times it occurs in a 180 day period.

Note that I understood your question to mean that you needed a *daily*
report on the preceeding quarter, so it gives an answer for each day.

 

[This is JIM ...]

Thank you for your quick response ! Will get a chance to try this
tomorrow - will let you know how it turned out ...

MUCH APPRECIATED
jim

 

[Jim]

Allen ..
Thank you very much - got it working ... EXCELLENT !!!

jim