• 0

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

## Question

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 ;^)

## Recommended Posts

• 0

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:

##### Share on other sites

• 0

@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:

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.