473,320 Members | 1,699 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 3440
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Aaron C | last post by:
Hi, I'm trying to do an insert with the following statement: INSERT INTO user VALUES ( 'ag@ag.com','ag','Aaron','Chandler','','','La Mirada','CA',90638,714,'',''); and I'm getting the error...
5
by: Cro | last post by:
Hello Access Developers, I'd like to know if it is possible to perform a count in an expression that defines a control source. My report is based on a query. In my report, I want a text box to...
1
by: Chris Wolfe | last post by:
I have two fields that draw their combobox values from the same table. LogBook04.Insurance is the Primary Insurance and LogBook04.SecIns is the Secondary Insurance. Both draw their values from...
4
by: Frank O'Neil via AccessMonster.com | last post by:
I have two forms. Form-1 has a combo box, the selection from the combo box populate form-2 Example form-1 Form- 2 Text1 Text2 Text3 Drop down contain text...
1
by: sunilkeswani | last post by:
Hi I am still new to access. I want to know how i can build a query which can display results from 4 different columns/fields Like. Field1 Field2 Field3 Field4 1 2 1 ...
0
by: midnight_use_only | last post by:
although i don't think this is do-able using SQL, i would ask and maybe an expert can help me out. assum i have the following table: Date Code Count 2006-06-06 abc 1234...
3
by: mkjets | last post by:
I have worked for hours on trying to find a solution and have not figured it out. I am working in Access 2003. I need to create a query that takes values from 1 table and displays them in...
22
by: MP | last post by:
vb6,ado,mdb,win2k i pass the sql string to the .Execute method on the open connection to Table_Name(const) db table fwiw (the connection opened via class wrapper:) msConnString = "Data Source="...
1
newnewbie
by: newnewbie | last post by:
Desperately need help in creating a query to count unique values in a table. I am a Business analyst with limited knowledge of Access….My boss got me ODBC connection to the underlying tables for our...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.