Count number of specfic charectors in access

I have an access database.
I have a combo to update the fields.
Now I need to count specific values and show in the form. Example below

field1   field2    field3    field4   field5   field6     field7    field8    field9    field10
   A          C           A+            null        D        null           E              E           B            null

A+, A, B  is good rating
C, D, E    is bad rating

I want to dispaly as below in the form
Good rating = 3
bad rating   = 4

Any one can help
LVL 30
MAS (MVE)EE Solution GuideAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You need a slight redesign of your database; this should have a related child table with a field of "rating".

If you had that, then you could use an SQL Count and get the answer easily.

With the layout the way you have it now, you have no choice but to visit every record and look at every field.

Jim.
0
MAS (MVE)EE Solution GuideAuthor Commented:
Can you explain if you dont mind?
0
Gustav BrockCIOCommented:
Here is a simple method using rounding down and up:

GoodRating: (1-Int(Asc(Nz([Field1],"Z"))/67))+(1-Int(Asc(Nz([Field2],"Z"))/67))+...+(1-Int(Asc(Nz([Field10],"Z"))/67))
BadRating: (-Int(-Asc(Nz([Field1],"A"))/66)-1)+(-Int(-Asc(Nz([Field1],"A"))/66)-1)+...+(-Int(-Asc(Nz([Field1],"A"))/66)-1)

Copy and insert for field 3 to 9.

/gustav
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

MAS (MVE)EE Solution GuideAuthor Commented:
Where I will mention A,A+,B , C etc ?
The above I have just given as a sample.  I will mention some other characters.  Can I do the same with this code?
0
Gustav BrockCIOCommented:
They are the field values from the field names you gave as example. That's all we have to work from.

/gustav
0
MAS (MVE)EE Solution GuideAuthor Commented:
A+, A, B  is good rating    and C, D, E    is bad rating .  It is saved as A+,A,B,C,D,E in database.

field1   field2    field3    field4   field5   field6     field7    field8    field9    field10
   A          A           A+            null        B        null           A              C           E            null
I want to dispaly as below in the form
Good rating = 5
bad rating   = 2
Will it work like that?
I mean using your code
0
Gustav BrockCIOCommented:
Yes, in a query.
In a form, you would use as ControlSource for the textboxes:

=(1-Int(Asc(Nz([Field1],"Z"))/67))+(1-Int(Asc(Nz([Field2],"Z"))/67))+...+(1-Int(Asc(Nz([Field10],"Z"))/67))
=(-Int(-Asc(Nz([Field1],"A"))/66)-1)+(-Int(-Asc(Nz([Field1],"A"))/66)-1)+...+(-Int(-Asc(Nz([Field1],"A"))/66)-1)

/gustav
0
MAS (MVE)EE Solution GuideAuthor Commented:
I attached the db with only that part.
Appreciate if you can make the changes as I am not clear

You do only decayed teeths with one or two  I will do the rest
Access-EE.accdb
0
Gustav BrockCIOCommented:
Oh, that is quite different and you have many more options.

You would expand the valuelists of the comboboxes to pairs of display and value, like:

"C";"1";"O";"1";"X";"0";"--";"1";"#";"0" ... etc.

Then set the ColomnCount of the comboboxes to 2 and the controlsource to:

=Nz([Combo172].[Column](1);0)+Nz([Combo173].[Column](1);0)+Nz([Combo174].[Column](1);0)  <etc>

to count the sound. Similar to count the decayed (could be counted from all-nulls-sound)

This is major piece of work to get done, and before doing so, you should really consider Jim's method of proper normalization. That will save you a lot of work later.
Also, it would be neater to write some code that loops all the comboboxes or works as a function that reads a full record and returns the value.

/gustav
0
Rey Obrero (Capricorn1)Commented:
test this
Access-EE.accdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MAS (MVE)EE Solution GuideAuthor Commented:
Thanks to Gustav and Jim

@Rey Obrero (Microsoft Access MVP)
Seems some code missing as it is counting wrong sometime.
Can you please double check the code.
0
Rey Obrero (Capricorn1)Commented:
<Seems some code missing as it is counting wrong sometime.>

post detail of what is missing..
0
MAS (MVE)EE Solution GuideAuthor Commented:
If you look at the attached DB you can see the first record calculation is correct and the second one is wrong.

In the second record it is supposed to be as below
TotDecay= 3   and  Totsound=6
Access-EE-1-.accdb
0
Rey Obrero (Capricorn1)Commented:
post the ASCII values of the symbols you are using.
0
MAS (MVE)EE Solution GuideAuthor Commented:
Copied from Form
Decays= •,  X, # ,  Ab
Sounds=   A, C, ∙,  O ,● ,--,  EO,  ES

Copied from VB editor
Decays= •,X,#,Ab
Sounds=  A,C,·,O,?,--,EO,ES
0
Rey Obrero (Capricorn1)Commented:
see that the character was changed.. that is why i ask you to post the ASCII character of the symbols you are using.
0
MAS (MVE)EE Solution GuideAuthor Commented:
Thanks
0
MAS (MVE)EE Solution GuideAuthor Commented:
Appreciate if you can explain the code used
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.