Rolling 30 Days Add in -1 Point

[joefonseca79]

I have a senerio where I have a Fact table that has the following
fields
FACT_ID, Emp_ID, Point, StartDate, Date_Plus30Days, Comments

The purpose of the app is to track every time a person misses a day of
work they get some Point # assigned to them.
If they don't call in and don't show they get 5 points.
If they don't show but call they get 2 points.

The issue that I'm having is that if someone misses work on 1/15/2009
and they get 5 points.
Then the next day they miss is on 2/28/2009 and they get 2 points.
Lets say they have one more missed day on 4/5/09 for 1 point.

The records in the table would look like the following.
FACT_ID, Emp_ID, Point, StartDate, Date_Plus30Days, Comments
1 1 5 1/15/09 2/15/09 Test 1
2 1 2 2/28/09 3/28/09 Test 2
3 1 1 4/5/09 5/5/09 Test 3

**I have an update query that goes in and populates the
Date_Plus30Days using the logic DateAdd("d",30,StartDate).

The time between 1/15/09 and 2/28/09 is more than 30 days.
I need a way to Insert a record for -1 every time their is a gap of
more than 30 days between entries.

If you could help me out that would be great or at least give me some
ideas on how to go about this.

Current Table Structure is:

Fact Table
FACT_ID, Emp_ID, Point, StartDate, Date_Plus30Days, Comments

Employee Table
Emp_ID, Employee, Employee_NBR

Calendar Table
Calendar_ID, Date, Month, Year

I have a current Append Query that inserts the -1 for every 30 days
based on a table that I created that has 1 date for each month. But
this isn't correct for the requirement.
They need it to insert for every instance where there is a 30 day span
between records.

INSERT INTO FACT ( POINT, EMP_ID, REASON, DATE_ENTERED )
SELECT -1 AS POINT, [(-1_LKUP) Query_ALL_CUST_MONTHS].EMP_ID, 'ADMIN
ENTERED' AS REASON, [(-1_LKUP) Query_ALL_CUST_MONTHS].DATE_ENTERED
FROM [(-1_LKUP) Query_ALL_CUST_MONTHS] LEFT JOIN FACT ON ([(-1_LKUP)
Query_ALL_CUST_MONTHS].EMP_ID=FACT.EMP_ID) AND (FORMAT([(-1_LKUP)
Query_ALL_CUST_MONTHS].DATE_ENTERED,"YYYYMM")=Format
(FACT.DATE_ENTERED,"YYYYMM"))
WHERE FACT.EMP_ID IS NULL;

 

[kc-mass]

Hi,
Have you looked at standardizing the 30 day period for everyoneYou could do
it with a function like the following air code:

Function SegmentBeginDate(DateMissed As Date)
SegmentBeginDate = #1/1/2006# + (30 * ((DateMissed - #1/1/2006#) \
30)) - 1
End Function

That would hand you back the start date for the 30 day period in which the
absence occured. That would make all absences comparable by standard
periods

Regards

Kevin

 

[kc-mass]

Hi,
Have you looked at standardizing the 30 day period for everyoneYou could do
it with a function like the following air code:

Function SegmentBeginDate(DateMissed As Date)
SegmentBeginDate = #1/1/2006# + (30 * ((DateMissed - #1/1/2006#) \
30)) - 1
End Function

That would hand you back the start date for the 30 day period in which the
absence occured. That would make all absences comparable by standard
periods

Regards

Kevin

 

[joefonseca79]

Hi,
Have you looked at standardizing the30day period for everyoneYou could do
it with a function like the following air code:

Function SegmentBeginDate(DateMissed As Date)
    SegmentBeginDate = #1/1/2006# + (30* ((DateMissed - #1/1/2006#)\30)) - 1
End Function

That would hand you back the start date for the30day period in which the
absence occured.  That would make all absences comparable by standard
periods

Regards

I have a senerio where I have a Fact table that has the following
fields
FACT_ID, Emp_ID, Point, StartDate, Date_Plus30Days, Comments

The purpose of the app is to track every time a person misses a day of
work they get some Point # assigned to them.
If they don't call in and don't show they get 5 points.
If they don't show but call they get 2 points.

The issue that I'm having is that if someone misses work on 1/15/2009
and they get 5 points.
Then the next day they miss is on 2/28/2009 and they get 2 points.
Lets say they have one more missed day on 4/5/09 for 1 point.

The records in the table would look like the following.
FACT_ID, Emp_ID, Point, StartDate, Date_Plus30Days, Comments
1          1       5      1/15/09    2/15/09         Test 1
2          1       2      2/28/09    3/28/09         Test 2
3          1       1      4/5/09     5/5/09          Test 3

**I have an update query that goes in and populates the
Date_Plus30Days using the logic DateAdd("d",30,StartDate).

The time between 1/15/09 and 2/28/09 is more than30days.
I need a way to Insert a record for -1 every time their is a gap of
more than30daysbetween entries.

If you could help me out that would be great or at least give me some
ideas on how to go about this.

Current Table Structure is:

Fact Table
FACT_ID, Emp_ID, Point, StartDate, Date_Plus30Days, Comments

Employee Table
Emp_ID, Employee, Employee_NBR

Calendar Table
Calendar_ID, Date, Month, Year

I have a current Append Query that inserts the -1 for every30days
based on a table that I created that has 1 date for each month.  But
this isn't correct for the requirement.
They need it to insert for every instance where there is a30day span
between records.

INSERT INTO FACT ( POINT, EMP_ID, REASON, DATE_ENTERED )
SELECT -1 AS POINT, [(-1_LKUP) Query_ALL_CUST_MONTHS].EMP_ID, 'ADMIN
ENTERED' AS REASON, [(-1_LKUP) Query_ALL_CUST_MONTHS].DATE_ENTERED
FROM [(-1_LKUP) Query_ALL_CUST_MONTHS] LEFT JOIN FACT ON ([(-1_LKUP)
Query_ALL_CUST_MONTHS].EMP_ID=FACT.EMP_ID) AND (FORMAT([(-1_LKUP)
Query_ALL_CUST_MONTHS].DATE_ENTERED,"YYYYMM")=Format
(FACT.DATE_ENTERED,"YYYYMM"))
WHERE FACT.EMP_ID IS NULL;

~~~ The 30 days is based on some HR policy and cna't be changed.

 

[joefonseca79]

Hi,
Have you looked at standardizing the30day period for everyoneYou could do
it with a function like the following air code:

Function SegmentBeginDate(DateMissed As Date)
    SegmentBeginDate = #1/1/2006# + (30* ((DateMissed - #1/1/2006#)\30)) - 1
End Function

That would hand you back the start date for the30day period in which the
absence occured.  That would make all absences comparable by standard
periods

Regards

I have a senerio where I have a Fact table that has the following
fields
FACT_ID, Emp_ID, Point, StartDate, Date_Plus30Days, Comments

The purpose of the app is to track every time a person misses a day of
work they get some Point # assigned to them.
If they don't call in and don't show they get 5 points.
If they don't show but call they get 2 points.

The issue that I'm having is that if someone misses work on 1/15/2009
and they get 5 points.
Then the next day they miss is on 2/28/2009 and they get 2 points.
Lets say they have one more missed day on 4/5/09 for 1 point.

The records in the table would look like the following.
FACT_ID, Emp_ID, Point, StartDate, Date_Plus30Days, Comments
1          1       5      1/15/09    2/15/09         Test 1
2          1       2      2/28/09    3/28/09         Test 2
3          1       1      4/5/09     5/5/09          Test 3

**I have an update query that goes in and populates the
Date_Plus30Days using the logic DateAdd("d",30,StartDate).

The time between 1/15/09 and 2/28/09 is more than30days.
I need a way to Insert a record for -1 every time their is a gap of
more than30daysbetween entries.

If you could help me out that would be great or at least give me some
ideas on how to go about this.

Current Table Structure is:

Fact Table
FACT_ID, Emp_ID, Point, StartDate, Date_Plus30Days, Comments

Employee Table
Emp_ID, Employee, Employee_NBR

Calendar Table
Calendar_ID, Date, Month, Year

I have a current Append Query that inserts the -1 for every30days
based on a table that I created that has 1 date for each month.  But
this isn't correct for the requirement.
They need it to insert for every instance where there is a30day span
between records.

INSERT INTO FACT ( POINT, EMP_ID, REASON, DATE_ENTERED )
SELECT -1 AS POINT, [(-1_LKUP) Query_ALL_CUST_MONTHS].EMP_ID, 'ADMIN
ENTERED' AS REASON, [(-1_LKUP) Query_ALL_CUST_MONTHS].DATE_ENTERED
FROM [(-1_LKUP) Query_ALL_CUST_MONTHS] LEFT JOIN FACT ON ([(-1_LKUP)
Query_ALL_CUST_MONTHS].EMP_ID=FACT.EMP_ID) AND (FORMAT([(-1_LKUP)
Query_ALL_CUST_MONTHS].DATE_ENTERED,"YYYYMM")=Format
(FACT.DATE_ENTERED,"YYYYMM"))
WHERE FACT.EMP_ID IS NULL;

~~~ The 30 days is based on some HR policy and cna't be changed.

 

[kc-mass]

Let's say you have an absence on 1/1, again on 1/15 and again on 2/10.

How would they get recorded? 2/10 is less than 30 days from next prior
absence but more than 30 from intial periond absence.

If all three are considered as in one period then I would just record the
incidents without the datePlus30days and do the calculations and data
grouping in some combination of query/VBA routine and report,

Hi,
Have you looked at standardizing the30day period for everyoneYou could do
it with a function like the following air code:

Function SegmentBeginDate(DateMissed As Date)
SegmentBeginDate = #1/1/2006# + (30* ((DateMissed - #1/1/2006#) \30)) - 1
End Function

That would hand you back the start date for the30day period in which the
absence occured. That would make all absences comparable by standard
periods

Regards

I have a senerio where I have a Fact table that has the following
fields
FACT_ID, Emp_ID, Point, StartDate, Date_Plus30Days, Comments

The purpose of the app is to track every time a person misses a day of
work they get some Point # assigned to them.
If they don't call in and don't show they get 5 points.
If they don't show but call they get 2 points.

The issue that I'm having is that if someone misses work on 1/15/2009
and they get 5 points.
Then the next day they miss is on 2/28/2009 and they get 2 points.
Lets say they have one more missed day on 4/5/09 for 1 point.

The records in the table would look like the following.
FACT_ID, Emp_ID, Point, StartDate, Date_Plus30Days, Comments
1 1 5 1/15/09 2/15/09 Test 1
2 1 2 2/28/09 3/28/09 Test 2
3 1 1 4/5/09 5/5/09 Test 3

**I have an update query that goes in and populates the
Date_Plus30Days using the logic DateAdd("d",30,StartDate).

The time between 1/15/09 and 2/28/09 is more than30days.
I need a way to Insert a record for -1 every time their is a gap of
more than30daysbetween entries.

If you could help me out that would be great or at least give me some
ideas on how to go about this.

Current Table Structure is:

Fact Table
FACT_ID, Emp_ID, Point, StartDate, Date_Plus30Days, Comments

Employee Table
Emp_ID, Employee, Employee_NBR

Calendar Table
Calendar_ID, Date, Month, Year

I have a current Append Query that inserts the -1 for every30days
based on a table that I created that has 1 date for each month. But
this isn't correct for the requirement.
They need it to insert for every instance where there is a30day span
between records.

INSERT INTO FACT ( POINT, EMP_ID, REASON, DATE_ENTERED )
SELECT -1 AS POINT, [(-1_LKUP) Query_ALL_CUST_MONTHS].EMP_ID, 'ADMIN
ENTERED' AS REASON, [(-1_LKUP) Query_ALL_CUST_MONTHS].DATE_ENTERED
FROM [(-1_LKUP) Query_ALL_CUST_MONTHS] LEFT JOIN FACT ON ([(-1_LKUP)
Query_ALL_CUST_MONTHS].EMP_ID=FACT.EMP_ID) AND (FORMAT([(-1_LKUP)
Query_ALL_CUST_MONTHS].DATE_ENTERED,"YYYYMM")=Format
(FACT.DATE_ENTERED,"YYYYMM"))
WHERE FACT.EMP_ID IS NULL;

~~~ The 30 days is based on some HR policy and cna't be changed.

 

[kc-mass]

Let's say you have an absence on 1/1, again on 1/15 and again on 2/10.

How would they get recorded? 2/10 is less than 30 days from next prior
absence but more than 30 from intial periond absence.

If all three are considered as in one period then I would just record the
incidents without the datePlus30days and do the calculations and data
grouping in some combination of query/VBA routine and report,

Hi,
Have you looked at standardizing the30day period for everyoneYou could do
it with a function like the following air code:

Function SegmentBeginDate(DateMissed As Date)
SegmentBeginDate = #1/1/2006# + (30* ((DateMissed - #1/1/2006#) \30)) - 1
End Function

That would hand you back the start date for the30day period in which the
absence occured. That would make all absences comparable by standard
periods

Regards

I have a senerio where I have a Fact table that has the following
fields
FACT_ID, Emp_ID, Point, StartDate, Date_Plus30Days, Comments

The purpose of the app is to track every time a person misses a day of
work they get some Point # assigned to them.
If they don't call in and don't show they get 5 points.
If they don't show but call they get 2 points.

The issue that I'm having is that if someone misses work on 1/15/2009
and they get 5 points.
Then the next day they miss is on 2/28/2009 and they get 2 points.
Lets say they have one more missed day on 4/5/09 for 1 point.

The records in the table would look like the following.
FACT_ID, Emp_ID, Point, StartDate, Date_Plus30Days, Comments
1 1 5 1/15/09 2/15/09 Test 1
2 1 2 2/28/09 3/28/09 Test 2
3 1 1 4/5/09 5/5/09 Test 3

**I have an update query that goes in and populates the
Date_Plus30Days using the logic DateAdd("d",30,StartDate).

The time between 1/15/09 and 2/28/09 is more than30days.
I need a way to Insert a record for -1 every time their is a gap of
more than30daysbetween entries.

If you could help me out that would be great or at least give me some
ideas on how to go about this.

Current Table Structure is:

Fact Table
FACT_ID, Emp_ID, Point, StartDate, Date_Plus30Days, Comments

Employee Table
Emp_ID, Employee, Employee_NBR

Calendar Table
Calendar_ID, Date, Month, Year

I have a current Append Query that inserts the -1 for every30days
based on a table that I created that has 1 date for each month. But
this isn't correct for the requirement.
They need it to insert for every instance where there is a30day span
between records.

INSERT INTO FACT ( POINT, EMP_ID, REASON, DATE_ENTERED )
SELECT -1 AS POINT, [(-1_LKUP) Query_ALL_CUST_MONTHS].EMP_ID, 'ADMIN
ENTERED' AS REASON, [(-1_LKUP) Query_ALL_CUST_MONTHS].DATE_ENTERED
FROM [(-1_LKUP) Query_ALL_CUST_MONTHS] LEFT JOIN FACT ON ([(-1_LKUP)
Query_ALL_CUST_MONTHS].EMP_ID=FACT.EMP_ID) AND (FORMAT([(-1_LKUP)
Query_ALL_CUST_MONTHS].DATE_ENTERED,"YYYYMM")=Format
(FACT.DATE_ENTERED,"YYYYMM"))
WHERE FACT.EMP_ID IS NULL;

~~~ The 30 days is based on some HR policy and cna't be changed.

 

[John Spencer]

One problem is where do you insert the -1 point? How is it recorded?
Does an employee get a -1 if they have NEVER missed a day? What if they
have two missed days one on Jan 1 2008 and a second on June 30 2008.
ADo they get only one -1 for the 180+ days that they did not miss a day
between the two dates and nothing for the period between June 30 2008
and May 27, 2009?

You can try the following - It may work to give you the count of points
that should be awarded.


SELECT A.EmpID, - Count(EmpID) as Points
FROM Table as A
WHERE A.Date_Plus30Days <
(SELECT NZ(Min(B.StartDate),#2999-12-31#)
FROM Table as B
WHERE B_Emp_ID = A.Emp_ID
AND B.StartDate > A.StartDate)
GROUP BY A.EmpID

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have a senerio where I have a Fact table that has the following
fields
FACT_ID, Emp_ID, Point, StartDate, Date_Plus30Days, Comments

The purpose of the app is to track every time a person misses a day of
work they get some Point # assigned to them.
If they don't call in and don't show they get 5 points.
If they don't show but call they get 2 points.

The issue that I'm having is that if someone misses work on 1/15/2009
and they get 5 points.
Then the next day they miss is on 2/28/2009 and they get 2 points.
Lets say they have one more missed day on 4/5/09 for 1 point.

The records in the table would look like the following.
FACT_ID, Emp_ID, Point, StartDate, Date_Plus30Days, Comments
1 1 5 1/15/09 2/15/09 Test 1
2 1 2 2/28/09 3/28/09 Test 2
3 1 1 4/5/09 5/5/09 Test 3

**I have an update query that goes in and populates the
Date_Plus30Days using the logic DateAdd("d",30,StartDate).

The time between 1/15/09 and 2/28/09 is more than 30 days.
I need a way to Insert a record for -1 every time their is a gap of
more than 30 days between entries.

If you could help me out that would be great or at least give me some
ideas on how to go about this.

Current Table Structure is:

Fact Table
FACT_ID, Emp_ID, Point, StartDate, Date_Plus30Days, Comments

Employee Table
Emp_ID, Employee, Employee_NBR

Calendar Table
Calendar_ID, Date, Month, Year

I have a current Append Query that inserts the -1 for every 30 days
based on a table that I created that has 1 date for each month. But
this isn't correct for the requirement.
They need it to insert for every instance where there is a 30 day span
between records.

INSERT INTO FACT ( POINT, EMP_ID, REASON, DATE_ENTERED )
SELECT -1 AS POINT, [(-1_LKUP) Query_ALL_CUST_MONTHS].EMP_ID, 'ADMIN
ENTERED' AS REASON, [(-1_LKUP) Query_ALL_CUST_MONTHS].DATE_ENTERED
FROM [(-1_LKUP) Query_ALL_CUST_MONTHS] LEFT JOIN FACT ON ([(-1_LKUP)
Query_ALL_CUST_MONTHS].EMP_ID=FACT.EMP_ID) AND (FORMAT([(-1_LKUP)
Query_ALL_CUST_MONTHS].DATE_ENTERED,"YYYYMM")=Format
(FACT.DATE_ENTERED,"YYYYMM"))
WHERE FACT.EMP_ID IS NULL;

 

[John Spencer]

One problem is where do you insert the -1 point? How is it recorded?
Does an employee get a -1 if they have NEVER missed a day? What if they
have two missed days one on Jan 1 2008 and a second on June 30 2008.
ADo they get only one -1 for the 180+ days that they did not miss a day
between the two dates and nothing for the period between June 30 2008
and May 27, 2009?

You can try the following - It may work to give you the count of points
that should be awarded.


SELECT A.EmpID, - Count(EmpID) as Points
FROM Table as A
WHERE A.Date_Plus30Days <
(SELECT NZ(Min(B.StartDate),#2999-12-31#)
FROM Table as B
WHERE B_Emp_ID = A.Emp_ID
AND B.StartDate > A.StartDate)
GROUP BY A.EmpID

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have a senerio where I have a Fact table that has the following
fields
FACT_ID, Emp_ID, Point, StartDate, Date_Plus30Days, Comments

The purpose of the app is to track every time a person misses a day of
work they get some Point # assigned to them.
If they don't call in and don't show they get 5 points.
If they don't show but call they get 2 points.

The issue that I'm having is that if someone misses work on 1/15/2009
and they get 5 points.
Then the next day they miss is on 2/28/2009 and they get 2 points.
Lets say they have one more missed day on 4/5/09 for 1 point.

The records in the table would look like the following.
FACT_ID, Emp_ID, Point, StartDate, Date_Plus30Days, Comments
1 1 5 1/15/09 2/15/09 Test 1
2 1 2 2/28/09 3/28/09 Test 2
3 1 1 4/5/09 5/5/09 Test 3

**I have an update query that goes in and populates the
Date_Plus30Days using the logic DateAdd("d",30,StartDate).

The time between 1/15/09 and 2/28/09 is more than 30 days.
I need a way to Insert a record for -1 every time their is a gap of
more than 30 days between entries.

If you could help me out that would be great or at least give me some
ideas on how to go about this.

Current Table Structure is:

Fact Table
FACT_ID, Emp_ID, Point, StartDate, Date_Plus30Days, Comments

Employee Table
Emp_ID, Employee, Employee_NBR

Calendar Table
Calendar_ID, Date, Month, Year

I have a current Append Query that inserts the -1 for every 30 days
based on a table that I created that has 1 date for each month. But
this isn't correct for the requirement.
They need it to insert for every instance where there is a 30 day span
between records.

INSERT INTO FACT ( POINT, EMP_ID, REASON, DATE_ENTERED )
SELECT -1 AS POINT, [(-1_LKUP) Query_ALL_CUST_MONTHS].EMP_ID, 'ADMIN
ENTERED' AS REASON, [(-1_LKUP) Query_ALL_CUST_MONTHS].DATE_ENTERED
FROM [(-1_LKUP) Query_ALL_CUST_MONTHS] LEFT JOIN FACT ON ([(-1_LKUP)
Query_ALL_CUST_MONTHS].EMP_ID=FACT.EMP_ID) AND (FORMAT([(-1_LKUP)
Query_ALL_CUST_MONTHS].DATE_ENTERED,"YYYYMM")=Format
(FACT.DATE_ENTERED,"YYYYMM"))
WHERE FACT.EMP_ID IS NULL;