468,457 Members | 1,558 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,457 developers. It's quick & easy.

Count values across fields?

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
3 3197
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
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

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Chris Wolfe | last post: by
4 posts views Thread by Frank O'Neil via AccessMonster.com | last post: by
1 post views Thread by sunilkeswani | last post: by
reply views Thread by midnight_use_only | last post: by
22 posts views Thread by MP | last post: by
reply views Thread by NPC403 | last post: by
1 post views Thread by subhajit12345 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.