Date() help

[joecosmides]

We have sales employees in a table. The table has a field called
SalesHireDate which is a date field. I have a query and a formula on a
form that calculates their commissions. We have a ramp up commission
scheme which means they have to meet or exceed the following sales
number every month until the 4th month when they are treated as a
normal sales rep with normal quotas like this:

30 days from date of hire they must do $8,125 in sales to be at 100%
quota
31-60 days = $16,250 to be at 100% quota
61-90 days = $24,375 to be at 100% quota
90 to 120 days = $32,500 to be at 100% quota

Once they have been here for 120 days, (4 months) they always have to
meet or exceed the quota of $32,500 for as long as they work here.

All I have right now is a blank field I created on my form and the
control source is:
=Sum([TotalAEPaymentDue]/32500)

On the form I created I have to filter by AE's (sales person name). It
will show all of their sales for that month and if it is equal to
32,500 then it will show 100% or higher if higher sales figures etc.

My other problem that I can see coming is that we pay commissions on a
certain day every month. For example it's always the last Friday of
the month that we do commissions. My boss is going to want this date
to be the 1st date that is considered the beginning time for their
first month. So if someone was hired on 1/16/2008 and commissions are
due on 1/25/2008 then they will not be held accountable yet until the
next commission date at the end of next month. If they were hired on
or before the 15th of January, 2008 (in this example) then they would
be accountable for that 1st commission date. Basically the 15th of
every month is the breaking point for the date hired.

 

[KARL DEWEY]

Create a table named Commissions like this --

Lo_Days Hi_Days Quota
0 30 8125
31 60 16250
61 90 24375
91 120 32500
121 9999999 32500

Use the query below in your =Sum([TotalAEPaymentDue]/[Commission Quota] )
formula ---

SELECT employeesTable.[emp name], employeesTable.SalesHireDate,
IIf(Day([SalesHireDate])>=16 And
Format([SalesHireDate],"yyyymm")=Format(Date(),"yyyymm"),1,[Quota]) AS
[Commission Quota], DateDiff("d",[SalesHireDate],Date())+1 AS Hire_Days,
Commissions.Lo_Days, Commissions.Hi_Days
FROM Commissions, employeesTable
WHERE (((DateDiff("d",[SalesHireDate],Date())+1) Between [Lo_Days] And
[Hi_Days]))
ORDER BY employeesTable.SalesHireDate DESC;
This takes the above into account with the IIF statement.
--
KARL DEWEY
Build a little - Test a little

We have sales employees in a table. The table has a field called
SalesHireDate which is a date field. I have a query and a formula on a
form that calculates their commissions. We have a ramp up commission
scheme which means they have to meet or exceed the following sales
number every month until the 4th month when they are treated as a
normal sales rep with normal quotas like this:

30 days from date of hire they must do $8,125 in sales to be at 100%
quota
31-60 days = $16,250 to be at 100% quota
61-90 days = $24,375 to be at 100% quota
90 to 120 days = $32,500 to be at 100% quota

Once they have been here for 120 days, (4 months) they always have to
meet or exceed the quota of $32,500 for as long as they work here.

All I have right now is a blank field I created on my form and the
control source is:
=Sum([TotalAEPaymentDue]/32500)

On the form I created I have to filter by AE's (sales person name). It
will show all of their sales for that month and if it is equal to
32,500 then it will show 100% or higher if higher sales figures etc.

My other problem that I can see coming is that we pay commissions on a
certain day every month. For example it's always the last Friday of
the month that we do commissions. My boss is going to want this date
to be the 1st date that is considered the beginning time for their
first month. So if someone was hired on 1/16/2008 and commissions are
due on 1/25/2008 then they will not be held accountable yet until the
next commission date at the end of next month. If they were hired on
or before the 15th of January, 2008 (in this example) then they would
be accountable for that 1st commission date. Basically the 15th of
every month is the breaking point for the date hired.

 

[joecosmides]

Create a table named Commissions like this --

Lo_Days Hi_Days Quota
0       30      8125
31      60      16250
61      90      24375
91      120     32500
121     9999999 32500

Use the query below in your =Sum([TotalAEPaymentDue]/[Commission Quota] )
formula ---

SELECT employeesTable.[emp name], employeesTable.SalesHireDate,
IIf(Day([SalesHireDate])>=16 And
Format([SalesHireDate],"yyyymm")=Format(Date(),"yyyymm"),1,[Quota]) AS
[Commission Quota], DateDiff("d",[SalesHireDate],Date())+1 AS Hire_Days,
Commissions.Lo_Days, Commissions.Hi_Days
FROM Commissions, employeesTable
WHERE (((DateDiff("d",[SalesHireDate],Date())+1) Between [Lo_Days] And
[Hi_Days]))
ORDER BY employeesTable.SalesHireDate DESC;

This takes the above into account with the IIF statement.
--
KARL DEWEY
Build a little - Test a little

We have sales employees in a table. The table has a field called
SalesHireDate which is a date field. I have a query and a formula on a
form that calculates their commissions. We have a ramp up commission
scheme which means they have to meet or exceed the following sales
number every month until the 4th month when they are treated as a
normal sales rep with normal quotas like this:

30 days from date of hire they must do $8,125 in sales to be at 100%
quota
31-60 days = $16,250 to be at 100% quota
61-90 days = $24,375 to be at 100% quota
90 to 120 days = $32,500 to be at 100% quota

Once they have been here for 120 days, (4 months) they always have to
meet or exceed the quota of $32,500 for as long as they work here.

All I have right now is a blank field I created on my form and the
control source is:
=Sum([TotalAEPaymentDue]/32500)

On the form I created I have to filter by AE's (sales person name). It
will show all of their sales for that month and if it is equal to
32,500 then it will show 100% or higher if higher sales figures etc.

My other problem that I can see coming is that we pay commissions on a
certain day every month. For example it's always the last Friday of
the month that we do commissions. My boss is going to want this date
to be the 1st date that is considered the beginning time for their
first month. So if someone was hired on 1/16/2008 and commissions are
due on 1/25/2008 then they will not be held accountable yet until the
next commission date at the end of next month. If they were hired on
or before the 15th of January, 2008 (in this example) then they would
be accountable for that 1st commission date. Basically the 15th of
every month is the breaking point for the date hired.- Hide quoted text -

- Show quoted text -

I'll try it out. Thanks a lot!