counting text character across rows in Access

[Guest]

I am trying to do a conditional count of text characters across rows to total
the number of "Y" or "N". I've several years of data in this format, so
cannot deviate the formating. I am looking to do the following:

Example: I wanted a total of "Y" in the following format in Access:

Y N Y N Y Y =4
N Y N Y N N =2

How do I get the total counts (4,2 etc) I've tried Dcount, Iif statements
etc. I was told I might need a VB code to do this. Any help is appreciated.
Hope you find this challeging.

 

[Guest]

I just tried this from the discussion & it worked:

Total of Y: IIf([Test1]="Y",1,0)+IIf([Test2]="Y",1,0) ......
IIf([Test6]="Y",1,0)

if there is a better way to do this, post your comments.

 

[Jeff Boyce]

Do you have a text string without any "dividers" between the "Y"s and "N"s,
or are there spaces or some other character between them? Is this
consistent?

If you do have delimiters, you could consider importing into Excel and using
the "parse" function to split the string into individual cells, each
containing a single "Y" or "N".

If you have to do this inside of Access, you'll need to learn enough about
VBA to create a procedure that accepts a string and counts the number of
characters that match a character you supply. The general approach
(non-functional, not-even-psuedo-code) is:

Provide the function a string of text and a character to look for
Make the first character the selected character
Start on the selected character
Compare to the "look for" character
If they match, add one to the Count-of-hits
When you're done with the first character, add one to the position you

are checking and go back to the third step, UNLESS there are no more
characters in the string

Report back the total Count-of-hits

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

The text are in separate fields. I tried the following & it worked. (The
Test1, Test2 ...etc are different text fields which hold the text string "Y")
:

Total of Y: IIf([Test1]="Y",1,0)+IIf([Test2]="Y",1,0) ............
+IIf([Test6]="Y",1,0)

However, I would like to get rid of typing so many fields. I tried but
didn't work :

IIf( Between [Test1] And [Test6]="Y",1,0

If you can make the above work too, it will save several hours of work for
me. Thanks!

 

[Douglas J Steele]

Why is it in 6 separate fields? That sounds like a repeating group, which is
a violation of database normalization rules. It's far easier to have 6 rows
in a child table, rather than 1 row with 6 repeating fields.

If you're stuck with that design, you can try normalizing your data through
the use of a UNION query:

SELECT Id, FIeld1, Field2, 1 As TestNumber, Test1 As TestResult
FROM MyTable
UNION
SELECT Id, FIeld1, Field2, 2 As TestNumber, Test2 As TestResult
FROM MyTable
UNION
SELECT Id, FIeld1, Field2, 3 As TestNumber, Test3 As TestResult
FROM MyTable
....
UNION
SELECT Id, FIeld1, Field2, 6 As TestNumber, Test6 As TestResult
FROM MyTable

Now, it's a simple query to determine how many Yeses were answered for each
Id:

SELECT Id, Field1, Field2, Abs(Sum([TestResult]))
FROM MyUnionQuery
GROUP BY Id, Field1, Field2

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The text are in separate fields. I tried the following & it worked. (The
Test1, Test2 ...etc are different text fields which hold the text string "Y")
:

Total of Y: IIf([Test1]="Y",1,0)+IIf([Test2]="Y",1,0) ............
+IIf([Test6]="Y",1,0)

However, I would like to get rid of typing so many fields. I tried but
didn't work :

IIf( Between [Test1] And [Test6]="Y",1,0

If you can make the above work too, it will save several hours of work for
me. Thanks!

Do you have a text string without any "dividers" between the "Y"s and "N"s,
or are there spaces or some other character between them? Is this
consistent?

If you do have delimiters, you could consider importing into Excel and using
the "parse" function to split the string into individual cells, each
containing a single "Y" or "N".

If you have to do this inside of Access, you'll need to learn enough about
VBA to create a procedure that accepts a string and counts the number of
characters that match a character you supply. The general approach
(non-functional, not-even-psuedo-code) is:
you
are checking and go back to the third step, UNLESS there are no more
characters in the string

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

You can use the following logic to compute the total Ys for a given row:

Abs(([Test1]="Y")+([Test2]="Y")+([Test3]="Y")+([Test4]="Y")+([Test5]="Y")+([Test6]="Y"))

E.G.

The following query would produce a list of the total number of Ys on each
row:

SELECT
Abs(([Test1]="Y")+([Test2]="Y")+([Test3]="Y")+([Test4]="Y")+([Test5]="Y")+([Test6]="Y"))
AS TotalYs
FROM
TableName;

The following query would give the total number of Ys for all rows:

SELECT
Sum(Abs(([Test1]="Y")+([Test2]="Y")+([Test3]="Y")+([Test4]="Y")")+([Test5]="Y")+([Test6]="Y")))
AS Totals
FROM
TableName;

Brian Mondoux

 

[Guest]

Thanks everyone. I did a combination of suggetions & one of them works now.
I'll investigate further. Without the community, I would have been lost for
days!!

You can use the following logic to compute the total Ys for a given row:

Abs(([Test1]="Y")+([Test2]="Y")+([Test3]="Y")+([Test4]="Y")+([Test5]="Y")+([Test6]="Y"))

E.G.

The following query would produce a list of the total number of Ys on each
row:

SELECT
Abs(([Test1]="Y")+([Test2]="Y")+([Test3]="Y")+([Test4]="Y")+([Test5]="Y")+([Test6]="Y"))
AS TotalYs
FROM
TableName;

The following query would give the total number of Ys for all rows:

SELECT
Sum(Abs(([Test1]="Y")+([Test2]="Y")+([Test3]="Y")+([Test4]="Y")")+([Test5]="Y")+([Test6]="Y")))
AS Totals
FROM
TableName;

Brian Mondoux

I am trying to do a conditional count of text characters across rows to
total
the number of "Y" or "N". I've several years of data in this format, so
cannot deviate the formating. I am looking to do the following:

Example: I wanted a total of "Y" in the following format in Access:

Y N Y N Y Y =4
N Y N Y N N =2

How do I get the total counts (4,2 etc) I've tried Dcount, Iif statements
etc. I was told I might need a VB code to do this. Any help is
appreciated.
Hope you find this challeging.