Count fields that are not null

[Kev]

Hello all,
I have an Access 2003 database running on an XP network.
I have a subform containing a 28 day roster - shift1 to shift28.
I need to total the shifts for each employee and am unsure of how to
do thid.
Each record has 1 EmployeeNumber, 28 shift fields, and 1 shiftTotal
field and 1 HoursTotal field.
I investigated the Count function but I think this Counts on the basis
of a field (Column) not a Record.
Does anyone have an idea how I should tackle this problem or am I on
the wrong track altogether?
Regards
Kevin

[engles]

On Mar 19, 11:10 pm, "Kev" <[e-mail]:

Hello all,
I have an Access 2003 database running on an XP network.
I have a subform containing a 28 day roster - shift1 to shift28.
I need to total the shifts for each employee and am unsure of how to
do thid.
Each record has 1 EmployeeNumber, 28 shift fields, and 1 shiftTotal
field and 1 HoursTotal field.
I investigated the Count function but I think this Counts on the basis
of a field (Column) not a Record.
Does anyone have an idea how I should tackle this problem or am I on
the wrong track altogether?
Regards
Kevin

I think you want something like sum (iif (shift1, 1, 0). This assumes
shift1 is boolean of course. Count won't work as you found out.

[Kev]

I think you want something like sum (iif (shift1, 1, 0). This assumes
shift1 is boolean of course. Count won't work as you found out.

Hello All,
Unfortunately Eng, Shift1 through to Shift28 are all text, not
boolean.
I still do not understand how your solution would count or sum the
shifts for a record (datasheet row). Isn't your solution summing all
the non nulls for the shift1 field (the column).
Sorry if I'm missing something which I obviously am.
I was thinking of something like the function below in the afterUpdate
event procedure but it seems like a lot of code and processing each
time someone makes a change:
Does anyone know of a more efficient method? I've never used the
Select Case statement before, would this be appropriate or is there a
predefined function for doing this?

Regards
Kevin

Public Function ShiftCounter()
Dim shiftCount As Long
shiftCount = 0
If (IsNull(Shift1)) Then
Else
shiftCount = shiftCount + 1
End If
If (IsNull(Shift2)) Then
Else
shiftCount = shiftCount + 1
End If
If (IsNull(Shift3)) Then
Else
shiftCount = shiftCount + 1
End If
If (IsNull(Shift4)) Then
Else
shiftCount = shiftCount + 1
End If
If (IsNull(Shift5)) Then
Else
shiftCount = shiftCount + 1
End If
Me![ShiftTotal] = shiftCount

End Function

[engles]

On Mar 20, 3:52 pm, "Kev" <[e-mail]:

I think you want something like sum (iif (shift1, 1, 0). This assumes
shift1 is boolean of course. Count won't work as you found out.

Hello All,
Unfortunately Eng, Shift1 through to Shift28 are all text, not
boolean.
I still do not understand how your solution would count or sum the
shifts for a record (datasheet row). Isn't your solution summing all
the non nulls for the shift1 field (the column).
Sorry if I'm missing something which I obviously am.
I was thinking of something like the function below in the afterUpdate
event procedure but it seems like a lot of code and processing each
time someone makes a change:
Does anyone know of a more efficient method? I've never used the
Select Case statement before, would this be appropriate or is there a
predefined function for doing this?

Regards
Kevin

Public Function ShiftCounter()
Dim shiftCount As Long
shiftCount = 0
If (IsNull(Shift1)) Then
Else
shiftCount = shiftCount + 1
End If
If (IsNull(Shift2)) Then
Else
shiftCount = shiftCount + 1
End If
If (IsNull(Shift3)) Then
Else
shiftCount = shiftCount + 1
End If
If (IsNull(Shift4)) Then
Else
shiftCount = shiftCount + 1
End If
If (IsNull(Shift5)) Then
Else
shiftCount = shiftCount + 1
End If
Me![ShiftTotal] = shiftCount

End Function

Have you thought about arranging your data differently? I would have
defined a separate table called "Shift" with field to indicate the
shift in it. Its foreign key is to the employees table. The fact
that the record is in the Shift table for the employee means that
employee works that shift. Then you can join and count directly.

[engles]

On Mar 20, 3:52 pm, "Kev" <[e-mail]:

I think you want something like sum (iif (shift1, 1, 0). This assumes
shift1 is boolean of course. Count won't work as you found out.

Hello All,
Unfortunately Eng, Shift1 through to Shift28 are all text, not
boolean.
I still do not understand how your solution would count or sum the
shifts for a record (datasheet row). Isn't your solution summing all
the non nulls for the shift1 field (the column).
Sorry if I'm missing something which I obviously am.
I was thinking of something like the function below in the afterUpdate
event procedure but it seems like a lot of code and processing each
time someone makes a change:
Does anyone know of a more efficient method? I've never used the
Select Case statement before, would this be appropriate or is there a
predefined function for doing this?

Regards
Kevin

Public Function ShiftCounter()
Dim shiftCount As Long
shiftCount = 0
If (IsNull(Shift1)) Then
Else
shiftCount = shiftCount + 1
End If
If (IsNull(Shift2)) Then
Else
shiftCount = shiftCount + 1
End If
If (IsNull(Shift3)) Then
Else
shiftCount = shiftCount + 1
End If
If (IsNull(Shift4)) Then
Else
shiftCount = shiftCount + 1
End If
If (IsNull(Shift5)) Then
Else
shiftCount = shiftCount + 1
End If
Me![ShiftTotal] = shiftCount

End Function

You can put that logic in a query.

shiftCount: iif(isnull(shift1),0,1) + iif (isnull(Shift2), 0, 1) + ...
+ iif (isnull(Shift28),0,1)

[Kev]

On Mar 21, 7:17 pm, [e-mail] wrote:

On Mar 20, 3:52 pm, "Kev" <[e-mail]:

I think you want something like sum (iif (shift1, 1, 0). This assumes
shift1 is boolean of course. Count won't work as you found out.

Hello All,
Unfortunately Eng, Shift1 through to Shift28 are all text, not
boolean.
I still do not understand how your solution would count or sum the
shifts for a record (datasheet row). Isn't your solution summing all
the non nulls for the shift1 field (the column).
Sorry if I'm missing something which I obviously am.
I was thinking of something like the function below in the afterUpdate
event procedure but it seems like a lot of code and processing each
time someone makes a change:
Does anyone know of a more efficient method? I've never used the
Select Case statement before, would this be appropriate or is there a
predefined function for doing this?

Regards
Kevin

Public Function ShiftCounter()
Dim shiftCount As Long
shiftCount = 0
If (IsNull(Shift1)) Then
Else
shiftCount = shiftCount + 1
End If
If (IsNull(Shift2)) Then
Else
shiftCount = shiftCount + 1
End If
If (IsNull(Shift3)) Then
Else
shiftCount = shiftCount + 1
End If
If (IsNull(Shift4)) Then
Else
shiftCount = shiftCount + 1
End If
If (IsNull(Shift5)) Then
Else
shiftCount = shiftCount + 1
End If
Me![ShiftTotal] = shiftCount

End Function

You can put that logic in a query.

shiftCount: iif(isnull(shift1),0,1) + iif (isnull(Shift2), 0, 1) + ...
+ iif (isnull(Shift28),0,1)

Thanks Eng,
This works really well and its fast and simple.
Really appreciate it, Thank you
Kevin