MS Access: Count Number of Fields Per Record That Contain Specific Value And Use This Count As Default Value of Another Field Within The Same Record

Question

Users import the serial number, then they are to fill the data for Error Name. Each serial number can have a maximum of 3 Error Names. The Error Names are then classified as either Type A or Type B.

Then there is the SumOfErrorTypeA field and SumOfErrorTypeB field. Users manually type in the Sum of each error type per record but our team needs to automate it. I tried IIFs, Dcounts, VBA math, to no avail... Is there a way to do it? Thank you!

Record SerialNumber Error1Name Error1Type Error2Name Error2Type Error3Name Error3Type SumOfErrorTypeA SumOfErrorTypeB
1      111111111    Error1     Type B     Error2     Type A     Error3     Type A     2               1
2      222222222    Error1     Type A     Error2     Type A                           2               0
3      333333333    Error1     Type A                                                 1               0
4      444444444    No error                                                          0               0 

I tried IIFs, DCounts, VBA, nothing worked.

Answer 1

You can calculate the sums:

select t.*,
       (iif(Error1Type = "A", 1, 0) +
        iif(Error2Type = "A", 1, 0) +
        iif(Error3Type = "A", 1, 0)
       ) as num_a,
       (iif(Error1Type = "B", 1, 0) +
        iif(Error2Type = "B", 1, 0) +
        iif(Error3Type = "B", 1, 0)
       ) as num_b
from t;

I'm not sure what you want to do with values, but this let's you calculate what they should be.