Date Calculation

[Christine]

Help!

I have a database that has fields for Absence Dates and Absence Value by
Employee Name. Value being .5 to 1 based upon the type. This data is being
entered via a form.

I need a calculation to add to the table to pull data for the current date
and look backwards in a rolling 12 month period. We are trying to look for
absences within a 3 month time frame and keep a running current calculation.
If no absences are incurred within 3 months the bank should be set back to 0.

if absences values are =3 (approaching a verbal warning) within a 3 month
if absence values are =4 (verbal warning issued)
if absences values are >4 but less than 7 (approaching written warning)
if absences values are =7 (written warning issued)
if absence values are >7 but less than 9 (approaching final written warning)
if absence values are = 9 (written final warning issued)
if absence values are >9 but less than 11 (review for termination)


I then need it to return a value to a field that can be shown on a form. I
am trying to have a data entry form that allows both data entry and provide
an overview by employee name of the current state of absences.

Is this possible?

 

[KARL DEWEY]

Create a table like this --
AbsenceAction --
CumValueLow CumValueHigh Action
0 3 Approaching a verbal warning
4 4 Verbal warning issued
4.0001 6.9999 Approaching written warning
7 7 Written warning issued
7.0001 8.9999 Approaching final written warning
9 9 Written final warning issued
9.0001 10.9999 Review for termination

Then two queries --
CumAbsences ---
SELECT Christine.[Employee Name], Sum(Christine.[Absence Value]) AS
[SumOfAbsence Value]
FROM Christine
WHERE (((Christine.[Absence Dates]) Between DateAdd("m",-3,Date()) And
Date()))
GROUP BY Christine.[Employee Name]
HAVING (((Sum(Christine.[Absence Value]))>=3));

SELECT CumAbsences.[Employee Name], CumAbsences.[SumOfAbsence Value],
AbsenceAction.Action
FROM CumAbsences, AbsenceAction
WHERE (((CumAbsences.[SumOfAbsence Value]) Between [CumValueLow] And
[CumValueHigh]));

 

[Larry Daugherty]

Hi Christine,

I see that you've received no response here after a day. The probable
reasons are:

You shifted your ground as you expressed what you wanted: "value of 5
to 1 based on type" yet you show possibilities that get to 9.

You want to store a calculated value that will certainly change over
time unless everyone has absolutely perfect attendance. Calculated
values shouldn't be stored in the database. It's just fine to show
calculated values in Forms and Reports but not to store them.
Calculate them each time you need them.

You specify a three month rolling window and then say you want to go
back a year??

You can gather and display information that covers the ground that you
seem to want but your post seems to have erroneous assumptions (as
applied to Access and any Relational Database Management System).

Below is a list of resources I've copied from one of John W. Vinson's
posts. I suggest that you start with Crystal's tutorials and then
tackle others as you see fit.

If you're serious about learning to develop with Access then I
recommend that you lurk:

microsoft.public.access.gettingstarted
microsoft.public.access.tablesdesign

Also, visit www.mvps.org/access it's chock full of Access lore.

HTH