Summing/Counting data in rows

[jgelpi16]

I have a table that has a list of employees. Going across I have a column for each week in the year. For each week the box can be "Y" or "N". What I want to do is count up the number of "Y"s for each employee and update the value within a field called "CountOfY". I'm struggling with getting the count of "Y"s. Can this be done with a cross-tab? Am I going to have to use a loop in VBA? What would be best method for obtaining this data? Any websites for research? Thanks for any help you can provide.

[ajetrumpet]

I don't think a crosstab will work. I believe that only works with one field, if you wanted to transform it into a calculation of somekind, but I could possibly be wrong.

vba though, wouldn't be that difficult, but on a large dataset it might take a while. for instance, to sum all of the fields that have a "yes" value in a boolean field for every record, you can simply write something like this:

select id, SumOfFields([id])...

and write a function like this:

Function SumOfFields(fldID As Long) As Long
                     
On Error GoTo ErrorHandler

Dim fld As Field
Dim fldSum As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("table name", dbOpenDynaset)

   rs.FindFirst "[ID] = " & fldID

For Each fld In rs.Fields
    If fld.Name <> "ID" Then
        if fld.value = -1 then 'OR "YES" will work too
            fldSum = fldSum + 1
        end if
    End If
Next fld

SumOfFields = fldSum

ErrorHandler:
   rs.Close
   db.Close
   Set rs = Nothing
   Set db = Nothing
   
End Function

[Rawb]

It sounds like you're making the classic "newbie" mistake of using a Table like a Spreadsheet. If you're really interested in setting up a normalized relational database I'm sure people here would be happy to help you out!

That said, your current approach can actually be made to work. I'd strongly recommend however, that you always calculate your number of "Y" weeks "on-the-fly" since it's constantly changing. With the speed of today's computers (even last decade's computers), this can be done almost instantly.

All you have to do is add the fields together using an IIf() statement. In the Control Source of your Form/Report, do the following:

=IIf([week1]="Y",1,0)+IIf([week2]="Y",1,0)+IIf([week3]="Y",1,0)

Just replace [week1], etc. with the actual field names used. If your field names were Jan1, Jan2, and Jan3 for instance, you'd use:

=IIf([Jan1]="Y",1,0)+IIf([Jan2]="Y",1,0)+IIf([Jan3]="Y",1,0)

If you're going to be saving the number to your Table though, I'd recommend using aje's VBA solution instead.

[ajetrumpet]

All you have to do is add the fields together using an IIf() statement. In the Control Source of your Form/Report, do the following:

=IIf([week1]="Y",1,0)+IIf([week2]="Y",1,0)+IIf([week3]="Y",1,0)

Just replace [week1], etc. with the actual field names used. If your field names were Jan1, Jan2, and Jan3 for instance, you'd use:
[CODE]
=IIf([Jan1]="Y",1,0)+IIf([Jan2]="Y",1,0)+IIf([Jan3]="Y",1,0)

I wasn't going to put the effort into explaining this exact solution, but this is exactly the problem that people run into when they treat Access like Excel. Inevitably, a question about "horizontal calculation" just like this pops up.

Believe me, I'm not reiterating what this man just said, but it's good for you to know jg, simply so you don't run into this situation again.

[jgelpi16]

Thanks for the input. Unfortunately I'm not sure there is another way to do what I am doing other than treating this as an excel table. I plan on obtaining the count each week and just appending the results to the field that corresponds with the employee. In all of my time using Access I have never attempted something like this. I don't think Rawb's method will work because I have 52 columns (one for each week in the year). I do like your method, aje, and thank you for the input. I am concerned with the processing requirement but I think I'll have to live with it as this is my only known option. Thanks again for the quick response and help.

[ajetrumpet]

post back if more ?'s pop up.

[jgelpi16]

Will do. Thank you.