Count Records Matching Two Fields

[FlyersFn32]

Sorry in advance...I've done a ton of searching and still can't figure this out

I am trying to make a layout that shows the number of records whose data matches two criteria. Fields are Ensemble and Score Order.

Looking to count number of records where Ensemble is Band and Score Order 1. I will then repeat that for score order being 2, 3, 4, etc.

Thank you so much for your help!

[bcooney]

many ways to do this, but need to clarify how you need to present these results. A report? A dashboard view?.

What is the  data model? Do you have a table with a record for each ensemble and a related table of score orders? That is, can an ensemble have one or more related score orders?

[comment]

9 hours ago, FlyersFn32 said:

I will then repeat that for score order being 2, 3, 4, etc.

This sounds like a report. If you sort the records by Ensemble and by Score Order, and show them using a layout that has only a sub-summary part when sorted by Score Order, then you can use a summary field defined as count of [any field that cannot be empty] to show the count of each subgroup. Note that this method counts only records in the current found set, so you might want to start this with Show All Records or a find.

If that's not you want, then please clarify as per @bcooney. Also please fill out your profile to reflect your version and OS so that we know what you can use.

 

[FlyersFn32]

10 hours ago, bcooney said:

many ways to do this, but need to clarify how you need to present these results. A report? A dashboard view?.

What is the  data model? Do you have a table with a record for each ensemble and a related table of score orders? That is, can an ensemble have one or more related score orders?

• • I am a school band director and I would like to create a report (simple list, really) showing me how many flutes, clarinets, saxophones, etc. I have in band at that point in time. If a few kids quit midyear, this dashboard would update automatically.

     For score order, flute is 1, clarinet is 2, and I will need to go up to 13.

     I have one table, and each record is a different student with their contact info, what instrument they play, and what ensembles they are in. Within that table are students in band, orchestra, and jazz band. So I would want a report that lists a count of students where ensemble = band and score order = 1, then score order = 2, all the way up to 13.

    Thank you!

    UpvoteReplySelect as Best

 

 

[comment]

5 hours ago, FlyersFn32 said:

I have one table, and each record is a different student with their contact info, what instrument they play, and what ensembles they are in.

A single table would be (just barely) adequate provided that:

1. Each student plays in at most one ensemble;
2. Each student plays at most one instrument.

If both of these conditions are true, then you can produce the report as I explained in my previous post - except that if you want it to list multiple ensembles, then you will also want a sub-summary part for Ensemble.

Note that I am talking about a report, not a "dashboard". A report is produced on demand by running a script, while usually people expect a dashboard to be "live" - i.e. depend on relationships and calculations only. This often takes up resources that decrease performance. I doubt you need this in the described situation (and I doubt even more you could produce it using only a single table). Luckily, with script triggers the perceived effect can be the same.