I am trying to get my Executesql() calculation to evaluate a count as part of a reporting structure of "active" records based on a request date and type of service from a portal. My parent table (Client_Layout) has my status record field, named STATUS. My portal, located on my parent layout, contains a Date and a category field named Services. Since my fields are in two different tables, I am unsuccessful in getting this to work. I have tried using the UNION clause to join the two tables and two Where clauses, but no success. I have tried using the HAVING clause but no success either. Here is how my calculation looks now: Somewhat strangely I have two Select statements. This was just an attempt at trying something else out.
"Select STATUS, Select Count(SERVICE)
From CLIENT_LAYOUT UNION JAIL_PORTAL
Where STATUS = 'Active'
Where SERVICE = 'Visit' and \"DATE\" >= ? and \"DATE\" <= ? " ; "" ; "" ; $$YTDFrom ; $$DateTo )
This calculation does work when I don't include the second table and the first Where clause where status = 'active'. But I need to combine
these two tables together where the status is equal to "active"
Appreciate any help. Thanks.