Counting a string in multiple fields

[JRickman]

[FONT=&quot]I have a query for a couple of tables in which I need to calculate the average behavior of students (4=good, …, 1=bad, 0=absent). Being new to Access, I need to know how to count the total number of “Absent” texts I find in the four “Attendance” fields (MonAttendance, TueAttendance, WedAttendance, ThuAttendance) and subtract that from 4 (the 4 days of Monday through Thursday), using the result as a divisor for the behavior total. I’ll then use that as a divisor for the total of the four behavior fields (MonBehavior, TueBehavior, WedBehavior, ThuBehavior). We gather these results on a weekly basis and need to run the query which will calculate this after receiving the data.[/FONT]

[FONT=&quot]What would be the easiest way to calculate this in an Access query? Is there a simple Count() procedure I can use? If so, how?[/FONT]

[FONT=&quot]Apologies for what is obviously a newbie question, but I'm just getting into Access. Thank you in advance for any help any of you can provide.[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Jim
[/FONT]

 

[MSAccessRookie]

First of all, Welcome to the Forum, Jim

Without explicit details, I can only suggest that the design that you are describing may be creating most of your difficulties. I envision something like this:

YourTable:
{SomeFields}
StudentID
MonAttendanceCode
TueAttendanceCode
WedAttendanceCode
ThuAttendanceCode
MonBehaviorCode
TueBehaviorCode
WedBehaviorCode
ThuBehaviorCode
{some other Fields}

Whenever you have Fields that serve the same purpose, it is most often best to have them in another table. Perhaps something like the following:

TblAttendance
AttendanceDay
StudentID
AttendanceCode

tblBehavior
AttendanceDay
StudentID
BehaviorCode

Or perhaps even

tblAttendance
Day
StudentID
AttendanceCode
BehaviorCode

This way, you can select all codes related to a student from a single Field.

 

[boblarson]

[FONT=&quot]What would be the easiest way to calculate this in an Access query? [/FONT]

The EASIEST way would be to have set up the table correctly where there is NOT 4 attendance fields but one and you have a row of data for each day as it occurs.

But in the absence of that you can determine the Absents by placing this code into a STANDARD module (not a form, report or class module):

Function WeeklyAbsences(ParamArray iArr() As Variant) As Integer
    Dim i As Integer
    Dim iCount As Integer
    For i = 0 To UBound(iArr)
        If iArr(i) = 0 Then
            iCount = iCount + 1
        End If
    Next
    WeeklyAbsences = iCount
End Function

Then in your query you can call it as another field


WeeklyAbsences([MonAttendance],[TueAttendance],[WedAttendance],[ThurAttendance]) AS Absences

But I would suggest going back and setting up the table structure correctly.

 

[JRickman]

Thanks for the quick reply.
As you suggest, perhaps my table design could use improvement. However, it seems I should be able to do what I want using the current design

StudentID ...12345

MonAttendance ... Present

TueAttendance ... Absent

WedAttendance ... Present

ThuAttendance ... Present

MonBehavior ... 4

TueBehavior ... 0

WedBehavior ... 4

ThuBehavior ... 4


What I'd like to do is count the number of "Absent" strings I find (1) and subtract it from 4 (the number of days in the week), getting 3, and use that as the divisor to the total of what is found in behavior (12) -- 12 / 3
Seems like there should be some sort of function which could be used to find the number of absences and use that as the divisor.
Is that possible and, if so, how?


Thanks, Jim

 

[boblarson]

Seems like there should be some sort of function which could be used to find the number of absences and use that as the divisor.
Is that possible and, if so, how?

Access is not Excel. You have to convert from Spreadsheet thinking to Relational Database thinking. In spreadsheet thinking you will think short and wide. But in relational database thinking you need to think thin and tall.

 

[JRickman]

[FONT=&quot]Admittedly, my table design is very Excel-like. End users fill out excel spreadsheets for their students and these spreadsheets are imported into the student table for each school. The spreadsheets have a record for each student to record the daily attendance, behavior, and other information, Monday through Thursday. Thus, a table with those fields seems most reasonable, especially given the fact that reports will be produced weekly based on that week’s data. Monthly, we will combine that month’s weekly data and calculate the results.[/FONT]
[FONT=&quot]I did create the module as was suggested, but it gives me a syntax error in the query. Perhaps I don't fully understand this, but I fail to see how the string "Absent" is detected by that module.[/FONT]
[FONT=&quot]Thanks, Jim
[/FONT]
[FONT=&quot]Thanks, Jim[/FONT]
[FONT=&quot]
[/FONT]

 

[boblarson]

[FONT=&quot]Perhaps I don't fully understand this, but I fail to see how the string "Absent" is detected by that module.[/FONT]

Oh, I thought you had a number in there. So, change to what I have changed from 0 to "Absent" like it shows in red:

Function WeeklyAbsences(ParamArray iArr() As Variant) As Integer
    Dim i As Integer
    Dim iCount As Integer
    For i = 0 To UBound(iArr)
        If iArr(i) = [B][COLOR=red]"Absent"[/COLOR][/B] Then
            iCount = iCount + 1
        End If
    Next
    WeeklyAbsences = iCount
End Function

 

[JRickman]

Thanks, that makes a whole lot more sense.
Okay, now I have one more problem. In the query,

WeeklyAbsences([MonAttendance],[TueAttendance],[WedAttendance],[ThurAttendance]) AS Absences

gives me a syntax error, with 'AS' in boldface.

Thank you for all your help.
Jim

 

[boblarson]

Thanks, that makes a whole lot more sense.
Okay, now I have one more problem. In the query,

WeeklyAbsences([MonAttendance],[TueAttendance],[WedAttendance],[ThurAttendance]) AS Absences

gives me a syntax error, with 'AS' in boldface.

Thank you for all your help.
Jim

That is the SQL version. To convert that into the graphical interface - the query designer (known as the Query By Example, or QBE grid) you would use:

Absences:WeeklyAbsences([MonAttendance],[TueAttendance],[WedAttendance],[ThurAttendance])

 

[JRickman]

I created the function using 'Module' in the Access Ribbon (I'm using Access 2010).

I then placed
Absences:WeeklyAbsences([MonAttendance],[TueAttendance],[WedAttendance],[ThurAttendance]
in the last field of the query (in design view).

When I ran the query, it errors out with "Undefined function 'WeeklyAbsences' in expression" error.

Must be doing something wrong.
Thanks, Jim

 

[boblarson]

Did you name the module the same as the function? It can't be the same.

 

[JRickman]

Yep, that's what I did... I changed the name and it runs like a charm.

Thank you for all your help.

Jim