Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

conditional count, count iif or iif count? 2

Status
Not open for further replies.

GaborH

Technical User
Nov 5, 2003
48
US
Forgive the basic question, but this is really driving me nuts.

I have a db that tracks payments. The txt field Initial_or_FollowOn designates if a payment is the first on a contract or not. I would like to Count the number of times a payment is flagged as “Initial” or “Follow-On” with respect to this txt field. Having tried a number of different combinations of Count and iif, cannot seem to set up the formula.

Can someone give me a hint? Any and all suggestions are highly appreciated.

Thank you,
 
ok, I'm going to assume that the fieldname in the database is Initial_or_FollowOn and it can contain 'Inital' or 'Follow-On'. Also assuming that there is an ID field that relates these payments to an entity like a person or a company.


SELECT COMPANYID, SUM(IIF(Initial_or_FollowOn = 'Initial' OR Initial_or_FollowOn = 'Follow On', 1, 0)) As Counter GROUP BY CompanyID

What this is doing is: If the field value is 'Initial' or 'Follow On', then return a 1 otherwise return a 0, then we sum all the responses.

HTH

leslie
 
Do you want these counts as they relate to each contract or do you want to know the total number for the time period without reference to the contract #?

The following code totals up the values without consideration of Contract#.

Code:
SELECT A.[Initial_or_FollowOn], Count(A.[Initial_or_FollowOn]) AS Count_InitialOrFollowOn 
FROM [Contract_Payments] as A 
GROUP BY A.[Initial_or_FollowOn] 
HAVING A.[Date_Field]Between [Enter BeginDate: ] and [Enter EndDate: ];


Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]


 
Two other approaches, without using direct sql:
1. Create a new query in query grid. Pull down the fields you want, e.g. companyId, Initial_or_FollowOn. Then click on the Sum feature. This will default to sorting the query By CompanId, then by Initial_or_FollowOn contents. Add a third field for counting records - some field that has a value in every record like CompanyId. Set this field to Count instead of GroupBy. Run query.
2. You can also set up report with a Select query as source. In the report, set the CompanyId and the Initial_or_FollowOn fields as GroupBy levels. Then if you want to see all the detail records within each group you can; or to see only group level Count values, set the Detail section Visible propery to No. Of source, include a formula to count the Initial_or_FollowOn contents (=Count([Initial_or_FollowOn]).
 
Thank you both for the quick replies. I wanted to total up the weekly list by this txt field, without regard to customerID, and you both have set me on the right track.

Thank you!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top