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

quinta-feira, 5 de novembro de 2009

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

--- In AccessVBACentral@yahoogroups.com, "Joe" <joefonseca79@...> wrote:
>
> 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;
>

ÇáÓáÇã Úáíßã
>if you want to record date exactly in table
>make two record start_date and last date
>in anew record calculate the diffrence between them
and give the points respect to only one month
------>Note :
>>your calculation applied to only one month