AnsweredAssumed Answered

Learning to use SQL query in calculation field

Question asked by halatkins on Jan 2, 2019
Latest reply on Jan 3, 2019 by philmodjunk

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:

 

Monthly_Stats::Total_Private_Cases

Monthly_Stats::Total_Public_Cases

Monthly_Stats::Total_Cases

 

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

 

     Cases:Type

     Cases:Date

 

"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?

Outcomes