Calculating Workdays 1

[serino]

I need some help again once again.
I have query which is based of a table called Jobtbl. My Table looks Like this..

JobNumber PhaseNumber CTCDTE FINISHDTE
302334 1 1/25/07 3/2/07
334455 2 3/5/07 3/8/07
312345 3 3/10/07 3/28/07


In my query a field called WRKDAYS calls a function called WorkDaysDifference that excludes weekends and holidays. This works well when I am calculating the number of workdays between [CTCDTE] and [FINISHDTE]. The function is..

[blue] WRKDAYS: IIf([Jobtbl]![PhaseNumber]=1,WorkDaysDifference([ctcdte],[FINISHDTE]))[/blue]

The query results are...

JobNumber PHASENUMBER CTCTDTE FINISHDTE WRKDAYS
302334 1 1/25/07 3/2/07 27
334455 2 1/25/07 3/8/07
312345 3 1/25/07 3/28/07

What I need to accomplish here is to calculate the WRKDAYS from the completion of PhaseNumber (1) 3/2/07 to the completion of PhaseNumber (2) 3/8/07 and so on... PhaseNumber (2) 3/8/07 through the completion of PhaseNumber (3) 3/28/07.

The results would be...

JobNumber PHASENUMBER CTCTDTE FINISHDTE WRKDAYS WRKDAYS2 WRKDAYS3
302334 1 1/25/07 3/2/07 27
334455 2 1/25/07 3/8/07 5
312345 3 1/25/07 3/28/07 15

How do I modify the funcion for WRKDAYS2 WRKDAYS3 to produce the above results?

 

[Remou]

You have omitted the code for WorkDaysDifference. You will either need to set up a sub query that returns the next record or else get the next record in the code.

I do not quite understand how you can tell these three phases are related as they have different job numbers, is there another field that shows these are related or is CTCTDTE the field that shows this?

 

[serino]

Remou,

Thank you for the reply.

My mistake on the jobnumbers they are the same.

JobNumber PHASENUMBER CTCTDTE FINISHDTE WRKDAYS WRKDAYS2 WRKDAYS3
302334 1 1/25/07 3/2/07 27
302334 2 1/25/07 3/8/07 5
302334 3 1/25/07 3/28/07 15


The function I am using a function was developed by Thom Rose.

[blue]Function WorkDaysDifference(dtStartDay As Date, dtEndDay As Date) As Long
'Function designed by Thom Rose - Permission to use is granted as long as you acknowledge the author
'This function calculates the number of workdays between two dates.
'The number of workdays is inclusive of the beginning and ending dates.
'There must be a table present called tblHolidays which contains the field dtObservedDate in the format date/time
'The table must list the dates of holidays to be observed.
'The user may include other fields in that table, for example, a description of the holiday being observed.

Dim lngTotalDays As Long
Dim lngTotalWeeks As Long
Dim dtNominalEndDay As Date
Dim lngTotalHolidays As Long

'Check to see if dtStartDay > dtEndDay. If so, then switch the dates
If dtStartDay > dtEndDay Then
dtNominalEndDay = dtStartDay
dtStartDay = dtEndDay
dtEndDay = dtNominalEndDay
End If
'Here are how many weeks are between the two dates
lngTotalWeeks = DateDiff("w", dtStartDay, dtEndDay)
'Here are the number of weekdays in that total week
lngTotalDays = lngTotalWeeks * 5
'Here is the date that is at the end of that many weeks
dtNominalEndDay = DateAdd("d", (lngTotalWeeks * 7), dtStartDay)
'Now add the number of weekdays between the nominal end day and the actual end day
While dtNominalEndDay <= dtEndDay
If WeekDay(dtNominalEndDay) <> 1 Then
If WeekDay(dtNominalEndDay) <> 7 Then
lngTotalDays = lngTotalDays + 1
End If
End If
dtNominalEndDay = dtNominalEndDay + 1
Wend
'Here are how many holiday days there are between the two days
lngTotalHolidays = DCount("dtObservedDate", "tblHolidays", "dtObservedDate <= #" & dtEndDay & "# AND dtObservedDate >= #" & dtStartDay & "# AND Weekday(dtObservedDate) <> 1 AND Weekday(dtObservedDate) <> 7")

'Here are how many total days are between the two dates - this is inclusive of the start and end date
WorkDaysDifference = lngTotalDays - lngTotalHolidays

End Function[/blue]

 

[PHV]

A starting point (SQL code):
SELECT A.JobNumber, A.CTCTDTE
, A.FINISHDTE AS Finish1, WorkDaysDifference(A.CTCTDTE,A.FINISHDTE) AS WRKDAYS1
, B.FINISHDTE AS Finish2, WorkDaysDifference(B.CTCTDTE,B.FINISHDTE) AS WRKDAYS2
, C.FINISHDTE AS Finish3, WorkDaysDifference(C.CTCTDTE,C.FINISHDTE) AS WRKDAYS3
FROM (Jobtbl AS A
INNER JOIN Jobtbl AS B ON A.JobNumber = B.JobNumber)
INNER JOIN Jobtbl AS C ON A.JobNumber = C.JobNumber
WHERE A.PhaseNumber = 1 AND B.PhaseNumber = 2 AND C.PhaseNumber = 3

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[serino]

PHV,

Thanks again, It worked perfectly!