Hot Fox: [Access VBA Central] Rolling 30 Days Add in -1 Point

sexta-feira, 22 de maio de 2009

[Access VBA Central] Rolling 30 Days Add in -1 Point

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;