Jump to content
  • 0

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


roattw
 Share

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)

caaspio1.jpg.e2ecd1a35d578c44522f2238d5590bfa.jpg

 

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

capio_desired.jpg.b55319e8a41a1785f9bc7dd65b533a17.jpg

 

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

caspio_pt_1.jpg.c9fbdab72806da6e6d432e8324880231.jpg

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

 

caspio_pt_2.jpg.e07b182c37ffa5c74675a93c3c9839b6.jpg

 

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

caspio_report.jpg.a7b9f0d1b204d97024b8f2e421ad4f05.jpg

 

All input and condolences welcome ;^)

 

Link to comment
Share on other sites

2 answers to this question

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]

ID4eQRm.png

The output:

2Mwb0vb.png

Link to comment
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]

ID4eQRm.png

The output:

2Mwb0vb.png

 

Link to comment
Share on other sites

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.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...