conditional count, count iif or iif count? 2

[GaborH]

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,

 

[lespaul]

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

 

[scriverb]

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#.

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]

 

[jjlogan]

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]).

 

[GaborH]

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!