Count totals from inside query rows


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]
FROM tPay4LeadsEnrolled
GROUP BY tPay4LeadsEnrolled.TerrName, IIf([Enrolled?]=True,"Yes","No"), "Need New Pct Here"
ORDER BY tPay4LeadsEnrolled.TerrName, IIf([Enrolled?]=True,"Yes","No") DESC;

Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Could you do a print screen of the design view of the query?  In this view, you can create a column that will you can sum and total the information you desire.
Not too sure about the field names, but how's this:
SELECT TerrName AS Territory, Format([Enrolled?], "Yes/No") AS [Enrolled Status],
Count(*) AS [Total FSAs],
(SELECT Count(*) FROM tPay4LeadsEnrolled WHERE TerrName = Primary.TerrName) AS [Region FSAs],
[Total FSAs] / [Region FSAs] * 100 AS [Regional %],
(SELECT Count(Contract) FROM tPay4LeadsEnrolled) AS [FSAs Offered],
[Total FSAs] / [FSAs Offered] * 100 AS [National %]
FROM tPay4LeadsEnrolled AS Primary
GROUP BY TerrName, [Enrolled?]

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AliciaVeeAuthor Commented:
Very cool!  I learned a few new things on this one.  I didn't know you can use Format on a Yes/No field -- I've used it many times for date fields, and I haven't seen a SELECT Count(*) before.  I'll have to research that one.  Your solution worked perfectly!  I only had to change the *100 (which I could have kept) but used the properties in the query to format the decimals to percents. Excellent!  Thank you!!
I learnt something too - I'd never tried re-using aliased expressions (such as "Count(*) AS [Total FSAs]") before; it really helps avoiding repeated expressions!

For what it's worth, I'd recommend using Count() in a sub-query rather than DCount(), as this keeps it all SQL.  You might want to bear the following in mind about Count():

1. Records with Null fields aren't counted unless the expression parameter is '*'
2. Count(*) is supposedly "considerably faster" than Count(<any field>)
3. Count(<any constant>) gives the same result as Count(<any mandatory field>) or Count(<any non-null expression>)

You may have noticed that I dropped some of your table qualifiers too - if there's only one table in your FROM clause it's clearer to use "TerrName" rather than "tPay4LeadsEnrolled.TerrName".  Lastly, there's no point having an ORDER BY clause that repeats an earlier GROUP BY clause.

Hope that explains how I got to my solution!  :-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.