Link to home
Start Free TrialLog in
Avatar of AliciaVee
AliciaVee

asked on

Count totals from inside query rows

Experts,

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

query.gif
Avatar of Glenn_Moore
Glenn_Moore

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.
ASKER CERTIFIED SOLUTION
Avatar of Jez Walters
Jez Walters
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of AliciaVee

ASKER

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!  :-)