By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,572 Members | 963 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,572 IT Pros & Developers. It's quick & easy.

Count fields that are not null

P: n/a
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

Mar 20 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
On Mar 19, 11:10 pm, "Kev" <kevin.vaug...@nhw.hume.org.auwrote:
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.

Mar 20 '07 #2

P: n/a
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

Mar 20 '07 #3

P: n/a
On Mar 20, 3:52 pm, "Kev" <kevin.vaug...@nhw.hume.org.auwrote:
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.

Mar 21 '07 #4

P: n/a
On Mar 20, 3:52 pm, "Kev" <kevin.vaug...@nhw.hume.org.auwrote:
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)

Mar 21 '07 #5

P: n/a
Kev
On Mar 21, 7:17 pm, eng...@ridesoft.com wrote:
On Mar 20, 3:52 pm, "Kev" <kevin.vaug...@nhw.hume.org.auwrote:
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

Mar 22 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.