Count values across fields?

Zb Bornemann

Hi All,

Ok. Here's what my table looks like:

Field1 Field2 Field3 Field4...
Record1 1 3 2 9
Record2 9 1 9 4
Record3 1 3 1 2 etc, etc.

What I want to do is for each record, count the number of values across the
fields. Specifically, I want to count the number of 9's in each record, so
in the above example, Record1 would have 1, Record2 would have 2 and Record3
would have 0.

I know how to count values in a particular field across records, but I can't
figure out how to do it across fields for each record. I've searched all
over the web and not found an answer. Has anybody here done it? If so,
how?

My SQL and VBA knowledge is limited, but I'm always up for learning new
things.

Thanks,
Zb
zbornema@ pitt.edu
(remove space from email address)

Nov 13 '05 #1

Rick Brandt

Zb Bornemann wrote:

Hi All,

Ok. Here's what my table looks like:

Field1 Field2 Field3 Field4...
Record1 1 3 2 9
Record2 9 1 9 4
Record3 1 3 1 2 etc, etc.

What I want to do is for each record, count the number of values
across the fields. Specifically, I want to count the number of 9's
in each record, so in the above example, Record1 would have 1,
Record2 would have 2 and Record3 would have 0.

I know how to count values in a particular field across records, but
I can't figure out how to do it across fields for each record. I've
searched all over the web and not found an answer. Has anybody
here done it? If so, how?

My SQL and VBA knowledge is limited, but I'm always up for learning
new things.

It would be ugly, but you could use Immediate-if functions...

NumberOfNines: IIf(field1=9,1,0) + IIf(field2=9,1,0), etc..

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Nov 13 '05 #2

Zb

Thanks for that suggestion. It's slightly more elegant than what I was
thinking I'd have to do.

What I'd really like to be able to do is define the set of fields as an
array and have Access count the number of 9's in the array. Stats packages
like SAS and SPSS can do this, so I was hoping Access could, too.

Would it make it any easier if I recoded the 9's to null values (since
that's what they represent)?

Thanks again,

Zb

"Rick Brandt" <ri*********@hotmail.com> wrote in message
news:35*************@individual.net...

It would be ugly, but you could use Immediate-if functions...

NumberOfNines: IIf(field1=9,1,0) + IIf(field2=9,1,0), etc..

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com Zb Bornemann wrote:

Hi All,

Ok. Here's what my table looks like:

Field1 Field2 Field3 Field4...
Record1 1 3 2 9
Record2 9 1 9 4
Record3 1 3 1 2 etc, etc.

What I want to do is for each record, count the number of values
across the fields. Specifically, I want to count the number of 9's
in each record, so in the above example, Record1 would have 1,
Record2 would have 2 and Record3 would have 0.

I know how to count values in a particular field across records, but
I can't figure out how to do it across fields for each record. I've
searched all over the web and not found an answer. Has anybody
here done it? If so, how?

My SQL and VBA knowledge is limited, but I'm always up for learning
new things.

Nov 13 '05 #3

Salad

Zb wrote:

Thanks for that suggestion. It's slightly more elegant than what I was
thinking I'd have to do.

What I'd really like to be able to do is define the set of fields as an
array and have Access count the number of 9's in the array. Stats packages
like SAS and SPSS can do this, so I was hoping Access could, too.

Would it make it any easier if I recoded the 9's to null values (since
that's what they represent)?

Thanks again,

Well, you could pass values to a function.

Public Function WhatsMyLen(strToCheck As String, _
strReplace As String) As Long

WhatsMyLen = _
Len(strToCheck) - Len(Replace(strToCheck,strReplace,""))
End Function

You could drop the above code into a module.

In the querybuilder you could enter something like
Expr1 : WhatsMyLen(Fld1 & fld2 & fld3,"9")

This concatenates the fields (fld1 to fld3) into a string and then
passes the string to WhatsMyLen. It also passes the number 9 as the
character to check for. The function gets the length of the string and
then gets the length of the string with the all of the "9"s removed and
subtracts the two.

If you know what value the get the count for is (in this example 9) and
that value is on a form, then in the query change the 9 to get the value
on the form. Ex:

Expr1 : WhatsMyLen(Fld1 & fld2 & fld3,Forms!YourForm!NineField)

This will work in A2000 and above.

Nov 13 '05 #4