You need a field to record ‘Supervisor Excuses the Missed Day’
tblEmpSchd is only for one week.  How do you track the week before?

I'm not sure quite how tblEmpSchd fits into this, so I'm ignoring it for
now.  If it's for people who work other than M-F, then you'll have to
massively re-write teh IsConsecutive function, and possibly the SickPoints
function.  For now, I'm assuming that all employees work M-F.  I've also
assumed that tblPointData already has entries in it for each EmployeeID and
Date for the last six months, and only needs to be updated with sick points.

Given that, I would structure the code something like this (this is VERY
light on details and probably won't do everything you need, but should give
you a rough outline to start from...it's also untested, so there may be bugs):

Public Function IsConsecutive(ByVal FirstDay As Date, ByVal NextDay As Date)
As Boolean
    'Depending on the structure of your query, etc., you may need to
    'expand this significantly to discount holidays, etc.
    IsConsecutive = ((NextDay - FirstDay) = 1) Or _
        (((NextDay - FirstDay) = 3) And _
        (Weekday(FirstDay, vbSunday) = vbFriday) And _
        (Weekday(NextDay, vbSunday) = vbMonday))
End Function

Public Function SickPoints(ByVal ThisDay As Date, ByVal PointsSoFar As
Double) As Double
    'Returns 1 or 1.5, depending on weekday
    'Or returns the difference if more than 2 points accumulated.
    'Thus stopping at 2 points.
    Dim dblSickPoints as Double
    Select Case Weekday(ThisDay, vbSunday)
        Case vbMonday, vbFriday
            dblSickPoints = 1.5
        Case Else
            dblSickPoints = 1
    End Select

    If PointsSoFar + dblSickPoints > 2 Then dblSickPoints = 2 - PointsSoFar
End Function

'Fragment of main code:  assumes table is open as recordset called rs,
'ordered by pdEmpID, pdClkIn.

Dim strEmpID As String
Dim dtClkIn As Date

With rs
    strEmpID = vbNullString
    dtClkIn = 0
    Do Until .EOF
        If strEmpID <> !pdEmpID.Value Then
            dblPtsSoFar = 0
            strEmpID = !pdEmpID.Value
            dtClkIn = !pdClkIn.Value
        End If
        If !pdType = "NOP S" Then
            If Not IsConsecutive(dtClkIn, !pdClkIn.Value) Then _
                dblPtsSoFar = 0
            !pdPoints.Value = SickPoints(!pdClkIn.Value, dblPtsSoFar)
            dblPtsSoFar = dblPtsSoFar + !pdPoints.Value
            .Update
        Else
            dblPtsSoFar = 0
        End
        .MoveNext
    Loop
End With

As I said, the above will definitely need some testing and debugging, as I'm
writing this completely off the top of my head, and there were things I
didn't necessarily understand.  But give it a whirl and see where it gets you.

Rob