Overall, here's what I'm trying to accomplish - I'm looking to build a table called "Monthly_Stats". As the name implied, I'd like this table to calculate and display some metrics (calculated from entries in another table "Cases").
In the "Monthly_Stats" table, here are the tables I'm using to get this started:
In "Montly_Stats", I'd like to have entries like "January 2019", "February 2019", "March 2019", etc. etc. To populate the stats in the fields above, I want to calculate the from entries in the table "Cases":
"Type" field gets its value from a value list - "Public" or "Private". "Date" is pretty obvious as well - the date of the Case.
So for the "January 2019" entry in Monthly_Stats, I'd like to have a calculation that will go hunting down the number of entries in "Cases" that match criteria for the number of private cases (in Monthly_Stats::Total_Private_Cases). In reading other posts in this community, I've seen the suggestion of using a SQL query to get this number - but I'm not getting anywhere with trying to build a query that gets me that number.
Is a SQL query the way to go?
Do these tables need to be related?