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) 3 3461
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
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.
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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".
|
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
fields so do not wish to have scores of queries to build my report.
|
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 the occurances of each type of insurance,
regardless of whether it is primary or secondary.
|
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
|
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
| |
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
|
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 regular
basis. I also need to use a query instead of a form because I then
need to be able to export...
|
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
moConn.Mode = adModeReadWrite' or using default...same result
|
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 programmer…and many queries that he wants me to do have a potential of being monstrous towers of...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
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 most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |