Counting a field match and sorting by another field value - simple but elusive to me

[roattw]

I might be making this harder than it is (which would be par for the course).  Thanks for any input!

Simple Table (publications) with the following fields:

Entry_ID  (autonumber)
Title     (text field)
Year     (text -  but also tried as number) entered as 2021, 2020, 2019
EM_FA (Text - brought in by radio button options YES and NO)
Resident    (Text - brought in by radio button options YES and NO)

 

Im trying to get this result.  Both category calculations grouped by Year.

 

But with Pivot Table using calculated field I cant get past this, doing each year one at a time:

OR this by adding Year as a Row: SAME Calculation applied to each field.

 

 

Or this using Tabular Report. Closer, but have to do each Year one by one.

 

All input and condolences welcome ;^)

 

[CoopperBackpack]

Hello @roattw,

As for me, the solution with the Pivot table and the Year field as a Row looks fine. 

Maybe I missed something in your description, but it seems that there is no need to hard code the Year value in the formula (for example, Year = '2021').

You may use a parameter instead. 

SELECT COUNT(EM_FA) FROM Publications WHERE EM_FA = 'YES' AND Year = target.[@field:Year]

The output:

[Tubby]

@roattw You might just be missing the "target." for your year field in the formula. This will avoid the same calculation applying to each row. Check @CoopperBackpack's formula.
 

18 hours ago, CoopperBackpack said:

Hello @roattw,

As for me, the solution with the Pivot table and the Year field as a Row looks fine. 

Maybe I missed something in your description, but it seems that there is no need to hard code the Year value in the formula (for example, Year = '2021').

You may use a parameter instead. 

SELECT COUNT(EM_FA) FROM Publications WHERE EM_FA = 'YES' AND Year = target.[@field:Year]

The output: