Using IIF statements with counts

  • Thread starter Thread starter FergusonH
  • Start date Start date
F

FergusonH

I am trying to have different paragraphs displayed in a report based on
whether or not a claimant has more than one claim. I am using a subreport to
pull in the claims for the entity. How would I set up the syntax to do the
following?

Claim number field = [Claim]
Claim amount = [Amount]
Entity = [Company]

If claimant has one claim...
=[Company] & "Per our record, your claim " & [Claim] & "has the amount of "
& [Amount] & " please confirm this matches..."

But if the claimant has more than one claim, I need to use a different
paragraph and then insert each claim in the paragraph. The claims are just in
a query/subreport under the entity's number. How can I get access to list
the next claim?

ie :
=[Company] & "Per our record, you filed multiple claims. Claim number
[Claim] & "has the amount of " & [Amount] & " . Your second claim, claim
number " & [Claim] & "in the amount of " & [amount]


Does this make sense? Can you help me? I've already asked if we could just
display all claims in a nice little chart but the user insists this goes in a
paragraph form.
 
One way you might try is first use a Ranking in Group to provide a number to
each claim.
SELECT Q.Company, Q.Item_no, Q.Claim, (SELECT COUNT(*) FROM [YourTable] Q1
WHERE Q1.[Company] = Q.[Company]
AND Q1.[Claim] <= Q.[Claim])+1 AS ClaimNUM
FROM YourTable AS Q
ORDER BY Q.Company, Q.Claim;

Use this query to list claims. It is set for maximum of 4 but all you need
is to continue to add instances of the ranking query and rank criteria.

Substitute Company for Group - Claim for Points - ClaimNUM for Rank

SELECT [Ranking in a group].Group, [Ranking in a group].Points, [Ranking in
a group_1].Points, [Ranking in a group_2].Points, [Ranking in a
group_3].Points
FROM (([Ranking in a group] LEFT JOIN [Ranking in a group] AS [Ranking in a
group_1] ON [Ranking in a group].Group = [Ranking in a group_1].Group) LEFT
JOIN [Ranking in a group] AS [Ranking in a group_2] ON [Ranking in a
group].Group = [Ranking in a group_2].Group) LEFT JOIN [Ranking in a group]
AS [Ranking in a group_3] ON [Ranking in a group].Group = [Ranking in a
group_3].Group
WHERE ((([Ranking in a group].Rank)=1)) OR ((([Ranking in a
group_1].Rank)=2)) OR ((([Ranking in a group_2].Rank)=3)) OR ((([Ranking in a
group_3].Rank)=4));

When you pull them together use an IIF statement to check for further claims
- if no more end with a period - otherwise a conjunction.
 
Back
Top