473,544 Members | 1,915 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 3450
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*********@ho tmail.com> wrote in message
news:35******** *****@individua l.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(strT oCheck As String, _
strReplace As String) As Long

WhatsMyLen = _
Len(strToCheck) - Len(Replace(str ToCheck,strRepl ace,""))
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!Your Form!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
10756
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 message "Column count doesn't match value count at row 1".
5
18241
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 display the number of times a certain value appears in a certain field (i.e. perform a ‘count'). I will be doing this for many values in many...
1
3436
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 tblInsurance.Insurance. The Primary Insurance or the Secondary Insurance could be any one of the values on the Insurance table. I'm trying to count...
4
2256
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 fields NA Sep Jar NA -- -- --- sep na
1
3666
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 1 1 0 0
0
1653
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 2006-06-06 abc 14 2006-06-06 abc 87 2006-06-06 xyz 1353 2006-06-06 xyz 19 2006-06-06 xyz 86
3
30272
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 multiple fields. I don't have the option of changing the structure of the existing tables because I am importing them from a separate data source on a...
22
12429
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=" & msDbFilename moConn.Properties("Persist Security Info") = False moConn.ConnectionString = msConnString moConn.CursorLocation = adUseClient...
1
3500
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 system and thinks I am omnipotent now and can extract any data out of it in the form he wants….The truth is, though I know SOME Access, I am not a...
0
7356
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7752
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7358
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7697
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
3397
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3396
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1831
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
980
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
650
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.