Rolling Time Periods

[dreamsoul620 via AccessMonster.com]

Hi,

The company I work for calculates points for an employee based on the first
occurence. There is no set start date for points (for instance, by quarter,
bi-annual, etc.). 60 days after this first occurence, the points are wiped
out. Points begin accumulating again with the next occurence.
Example: I could be late and accumulate 1 point on January 3rd. This would
be my 1st occurence. I could get another point on February 26th. This would
be 2 points for my 60 day period. On March 2nd, my points would reset to 0.
If I was to receive a point on July 5th, this would be my 1st occurence and
the 60 day window would begin again.

Is there anyway to calculate this through a query or multiple queries? I
have all the dates, reasons, and points for each occurence in a table. Any
help is greatly appreciated.

Thanks.

 

[Tom Ellison]

Dear dream:

If a date is supplied you could simply count only those points between that
date and 60 days prior to that date. It seems to me that would be
sufficient. Does that sound right to you.

The best way to supply this date is using a control on a form. The date in
that control could default to today's date, but there's a small possibility
that the system date could be in error, so letting the user see and correct
this date would be a good idea.

Then a query could be written to give a total as of whatever date is
supplied:

I don't expect this would take multiple queries. Do you want to see just
one row per employee with the total accumulation as of the selected date, or
do you want to see all the detail of the points in effect?

If you'll provide details, I could try to help write this.

Tom Ellison

 

[[MVP] S.Clark]

Create a table to hold the 4 date values, normalized, so that they're all in
the same column. Suppose tlkpStartDate.

To get the most recent start date:
Select Top 1 StartDate from tlkpStartDate where StartDate < Date() order
by startdate

Use that in your Group By query to calc the points

Select EmpID, Sum(Points) from tblBadEmployees
where OccurDate >= (Select Top 1 StartDate from tlkpStartDate where
StartDate < Date())

I think the subquery can be used here, since it is only returning one value.
If not, then there are more ways to do it, like call a public function that
returns the last startdate.

 

[dreamsoul620 via AccessMonster.com]

The dates are currently pulling based on punch times and the system's
absentee list. The user wants it set up so that the point detail shows in a
report. After a certain number of points, a warning alert is generated.
This will be a button on a form that will query the database daily for the
point accumulation. The user never actually enters a date. They want it to
be their automatically. Therein lies my dilema. Otherwise, your idea would
work great!

Dear dream:

If a date is supplied you could simply count only those points between that
date and 60 days prior to that date. It seems to me that would be
sufficient. Does that sound right to you.

The best way to supply this date is using a control on a form. The date in
that control could default to today's date, but there's a small possibility
that the system date could be in error, so letting the user see and correct
this date would be a good idea.

Then a query could be written to give a total as of whatever date is
supplied:

I don't expect this would take multiple queries. Do you want to see just
one row per employee with the total accumulation as of the selected date, or
do you want to see all the detail of the points in effect?

If you'll provide details, I could try to help write this.

Tom Ellison

Hi,

[quoted text clipped - 21 lines]

Thanks.

 

[dreamsoul620 via AccessMonster.com]

Their is a person keeping up with this in an Excel spreadsheet currently. If
I'm understanding correctly, I could pull the current start dates from there.
I could then have the end date as StartDate + 60 days to get my end date.
Would it be possible to archive or move this data from both my Points table
and the date table so that the oldest dates for each employee would
automatically be the new StartDate?
Example:
The list has David as having a start date of Jan. 3rd. His end date would
then be March 4. I would want to move all his data from Points and Dates
into archived versions of the tables (just in case the points need to be
reviewed later). The next time David appears on the Points table is May 5.
The Date table would see that as his oldest date and automatically calculate
the 60 day range.
Also, just to make certain, I would need to tie the Employee name or number
to these dates (Date table), correct?
I apologize if I'm asking for too much clarification. I'm still fairly new
to more advanced access techniques. I really appreciate the help.

Create a table to hold the 4 date values, normalized, so that they're all in
the same column. Suppose tlkpStartDate.

To get the most recent start date:
Select Top 1 StartDate from tlkpStartDate where StartDate < Date() order
by startdate

Use that in your Group By query to calc the points

Select EmpID, Sum(Points) from tblBadEmployees
where OccurDate >= (Select Top 1 StartDate from tlkpStartDate where
StartDate < Date())

I think the subquery can be used here, since it is only returning one value.
If not, then there are more ways to do it, like call a public function that
returns the last startdate.

Hi,

[quoted text clipped - 21 lines]

Thanks.

 

[Tom Ellison]

Dear dream:

My idea will still work, but you must determine a way to find what that date
should be.

One way to do this is to use the system date. This depends on that system
date being correct. My earlier suggestion does everything you asked for.
They do not have to enter a date. However, it is a very good idea to show
the user what the system date is, and to allow them to correct it if it is
wrong. If it is correct they do not have to do anything. They do not have
to enter the date at all, unless it is wrong. Why is this not a good thing?
It IS there automatically. It is just that the system does not then
automatically malfunction when the system date is screwed up, without
allowing the user to correct the problem. Why is that not a good thing?

Tom Ellison

The dates are currently pulling based on punch times and the system's
absentee list. The user wants it set up so that the point detail shows in
a
report. After a certain number of points, a warning alert is generated.
This will be a button on a form that will query the database daily for the
point accumulation. The user never actually enters a date. They want it
to
be their automatically. Therein lies my dilema. Otherwise, your idea
would
work great!

Dear dream:

If a date is supplied you could simply count only those points between
that
date and 60 days prior to that date. It seems to me that would be
sufficient. Does that sound right to you.

The best way to supply this date is using a control on a form. The date
in
that control could default to today's date, but there's a small
possibility
that the system date could be in error, so letting the user see and
correct
this date would be a good idea.

Then a query could be written to give a total as of whatever date is
supplied:

I don't expect this would take multiple queries. Do you want to see just
one row per employee with the total accumulation as of the selected date,
or
do you want to see all the detail of the points in effect?

If you'll provide details, I could try to help write this.

Tom Ellison

Hi,

[quoted text clipped - 21 lines]

Thanks.