Count Specific Values In Combo Boxes (1 Viewer)

[Smokeeater]

Have a table, with related query. All are combo boxes with set choices of G (Good) or NG (No Good), and then each subset it used to select a number of NG items.

Field 1 - G
Field 2 - NG
Field 3 - NG
Field 4 - G
Field 5 - G
Field 6 - G
NG Score = 2 (manually selected from 1-6, for the total number of fields).

There are 4 such structures in my table, and all works well. Trying to automate the [NG Score] field based on the number of corresponding NG items, so that a user does not actually select the wrong "answer". This number is also used to tally an overall score, etc.

Trying to determine how to look at all of the 6 G/NG fields and count the number that are NG. Number fields I can figure out easily, but trying to count "text" fields is giving me a headache.

Much appreciated for any assistance given!

 

[Mark_]

Your G/NG entries should (in a normalized database) be in child records. Makes this kind of calculation very easy.

That said, the "Easy" way is to make one query per field. Each query would be structured the same;
File_ID
File_Field (1,2,3,ect...)

Then you would join the (in this case) six queries together with unions. This will allow you to turn the six individual field into, effectively, records in a normalized database.

From there you can use functions such as DCount to return what you want.

 

[arnelgp]

You use an expression in the query:

NG total:Sum(Iif(field1="NG",1,0)+IIF(field2="NG",1,0)+...)

 

[Smokeeater]

Mark and arnelgp - thank you for your replies and guidance. My example showed just 6 of the fields, however, there are a total of 28 such fields divided into 4 groups so individual queries will not work. I tested your code Mark in a query and it works perfectly for what I need. Thanks again!

 

[Smokeeater]

Ouch.....well, now I am getting an error message on the query expression. "Query does not include the specified expression 'ID' as part of the aggregate function". Here is my expression:

Saf_Deviations_Total:Sum(IIF(Saf_Pledge="NG",1,0)+IIF(Saf_Tracking_Align="NG",1,0 )+ IIF(Saf_Injury_Tracking="NG",1,0 ) +IIF(Saf_Group_Layout="NG",1,0 ))

Not all of my fields have data (G or NG) in them. Some records only have NG noted, otherwise the field is left blank. Could that be causing the issue since the field is "null"?

I have fixed it for all new records by setting the default to "G", but am wondering if I need to have the end-user fill in "G's" for all of the completed records.

 

[arnelgp]

Add "" to all the expression

Sum(IIF(Saf_Pledge & ""="NG",1,0)+

 

[Smokeeater]

arnelgp: getting the same error message, and when I put the expression in the query, it is putting brackets around each of the field names. I tried removing them but the query reinserts them.

Saf_Deviations_Total: Sum(IIf([Saf_Pledge] & ""="NG",1,0)+IIf([Saf_Tracking_Align] & ""="NG",1,0)+IIf([Saf_Injury_Tracking] & ""="NG",1,0)+IIf([Saf_Group_Layout] & ""="NG",1,0))

 

[isladogs]

The issue is Sum - remove it and the outer brackets

Tey pasting this test code into a module and experiment with the Const values
It works for me

I've also provided alternative code using Nz which I personally prefer ...but both work as shown
I've also checked to confirm that the result is 0 if all 4 fields are blank as you would expect

Function TestMe() As Integer

Const Saf_Pledge = "G"
Const Saf_Tracking_Align = "NG"
Const Saf_Injury_Tracking = ""
Const Saf_Group_Layout = "NG"

 TestMe = IIf([Saf_Pledge] & "" = "NG", 1, 0) + IIf([Saf_Tracking_Align] & "" = "NG", 1, 0) _
    + IIf([Saf_Injury_Tracking] & "" = "NG", 1, 0) + IIf([Saf_Group_Layout] & "" = "NG", 1, 0)
    
 'alternative code
' TestMe = IIf(Nz([Saf_Pledge], "") = "NG", 1, 0) + IIf(Nz([Saf_Tracking_Align], "") = "NG", 1, 0) _
    + IIf(Nz([Saf_Injury_Tracking], "") = "NG", 1, 0) + IIf(Nz([Saf_Group_Layout], "") = "NG", 1, 0)
    
Debug.Print TestMe
    
End Function

 

[Smokeeater]

Thank you ridders!!

I used this and it works. I did not make the connection regarding the use of SUM. Very much appreciate your help!!

Saf_Deviations_Total: IIf([Saf_Pledge] & "" = "NG", 1, 0)+IIf([Saf_Tracking_Align] & "" = "NG", 1, 0)+ IIf([Saf_Injury_Tracking] & "" = "NG", 1, 0) + IIf([Saf_Group_Layout] & "" = "NG", 1, 0)

 

[isladogs]

You're welcome.
If you think about it, you were trying to do this

=SUM(1+0+1+1)

What you wanted was

=1+0+1+1

 

[Smokeeater]

Exactly....I just didn't think it through logically. Thanks again!

 

[MarkK]

You don't need an IIF() function to evaluate a boolean expression, which is already either a zero or minus one. Just use subtraction rather than addition to sum the negative values...

Saf_Deviations_Total: -(Saf_Pledge & "" = "NG") - (Saf_Tracking_Align & "" = "NG") - (Saf_Injury_Tracking & "" = "NG") - (Saf_Group_Layout) & "" = "NG")

If the expression is False it equals zero, if the expression is "Minus True" is equals one.
hth
Mark

 

[Smokeeater]

Thanks MarkK. I will give that a try. I like nice clean language so that if someone else needs to modify it, they can easily understand what is being done.