Count function

[dbertanjoli]

I have one more question. I want use count function but to count records from several fields =CDbl(Nz(Count([firstfield],[secondfield),0)). Where field is zero not to count it in. This function is working when I have only one field but when I add second I get an error message.

Many thanks for your help.

Debie

 

[boblarson]

This function is working when I have only one field but when I add second I get an error message.

Count is only good for ONE field. And, when you say records - a record in Access is a row of data.

If you want a count for each field then you need to use the count with each field separately.

 

[dbertanjoli]

Let say I have five fields and would like to count how many of them where answed related to particular patient (this total would go into mean calucation). How can I do this in report?

 

[boblarson]

So, are you meaning that the person could answer 3 of the 5 and you would want to have a total count of 3?

 

[dbertanjoli]

Exactly like that. I jsut tried this: = DCount("[13] & [17]", "") to concatenate fields but it shows error message. Any suggestions?

many thanks,
Deb

 

[boblarson]

Numeric field names like that can cause access to think you are doing something other than what you want. I would send the values to a VBA function as you can have a little more control that way.

 

[dbertanjoli]

It is a bit complex. My data is coming from the webform. The table name that holds answers is owsResponseAnswer. In this table I have only several fields: ResponseID, QuestionID, Date and Answers. There is a table called owsQuestion that has a field "QuestionDescription" and all these questions are generated on line and all these questions are listed under this field "QuestionDescription" but the only way to refer to them is by questionID. So basicly I needed to create crosstab query to have separate column for each question and answer so I can build a report and do calculations (each column heading is a question). I have to figure out how to count answered questions as it is important for further calculations and I AM LOST. Please help!
Deb

 

[boblarson]

That it is complex is not surprising because you didn't design the database in a properly normalized fashion. Yes, it is easier to collect the data that way, but way harder to do any meaningful reporting.

However, what you might be able to do is something like this:

Public Function GetCounts(lngResponseID As Long) As Integer
Dim rst As DAO.Recordset
Dim strSQL As String
Dim intCount As Integer
Dim intRS As Integer
Dim intQuestion As Integer

' select the row that matches the passed Response ID.
strSQL = "SELECT * FROM owsResponseAnswer WHERE ResponseID = " & lngResponseID

' opens a DAO recordset to get the values from 
Set rst = CurrentDb.OpenRecordSet(strSQL)

' sets the starting field number to look for filled in answers
intQuestion = 4

   ' Sets the loop to run through all of the fields of the recordset
   Do Until intRS = rst.Fields.Count
       ' check to see if the field is null or empty and if not then add that to the count
       If Len(rst(intQuestion) & "") <> 0 Then
          intCount = intCount + 1
       End If

       ' this increments the count for the iteration through the fields
       intRS = intRS + 1

       ' this increments the field number for checking for a value or non value
       intQuestion = intQuestion +1
   Loop

' sets the value of the function to the count of fields with an answer
GetCounts = intCount

' closes the recordset which should be done 
rst.Close
' removes the recordset variable from memory
Set rst = Nothing

The way this works is that it iterates through the fields of a single record called by the query. I set intQuestion = 4 to start with because according to your table description it looks like the answers start on the fourth field. Just change that starting number if it isn't. Also, you would call it from the query as:

MyCount:GetCounts([ResponseID])

Hopefully that will help.