Search code examples
vbams-access

COUNTIF in access Query

I have an Access Query that has computed columns as Rank1, Rank2, Rank3 which can be A, B or C as values.

Rank1   Rank2   Rank3   Final_Rank
  A       B       C
  B       A       A
  C       B       C

I want to add another column Final_Rank which use the COUNTIF excel formula. For instance, IIF(COUNTIF(Rank1:Rank3,"A")<3,"B",IIF(...))

Is it possible in Access ?

Solution

  • There is no function in MsAccess SQL that can count certain values across columns, like EXCEL's COUNTIF can. You can simulate it as:

    IIF(
   IIF(Rank1='A',1,0)+IIF(Rank2='A',1,0)+IIF(Rank3='A',1,0) < 3 , 'B', ...