I have a table with data that I need to track. The key field is Enrolled? with a yes/no value. Each record has a region name, and I need to obtain percents of how many are enrolling in a program, both at the region level, and also find the percent on the overall national level. I've attached an image -- the only column I don't have is column B -- that is the part I'm struggling with. Column E is not "correctly" in the query either, just a place holder -- since I don't have column B to do the calc with -- so I have manually provided the values so you can see what I'm looking to do. I used column F -- which gives me an national total based on a DCOUNT function which works well. I'll need both regional and national percents in one query if possible
SELECT tPay4LeadsEnrolled.TerrName AS Territory, IIf([Enrolled?]=True,"Yes","No") AS [Enrolled Status], Count(IIf([Enrolled?]=True,"Yes","No")) AS [Total FSAs], "Need New Pct Here" AS [Regional %], [Total FSAs]/[FSAs Offered] AS [National %], DCount("[Contract]","tPay4LeadsEnrolled") AS [FSAs Offered]
GROUP BY tPay4LeadsEnrolled.TerrName, IIf([Enrolled?]=True,"Yes","No"), "Need New Pct Here"
ORDER BY tPay4LeadsEnrolled.TerrName, IIf([Enrolled?]=True,"Yes","No") DESC;