Explain like I'm five: using IIf statements in a crosstab query

[imaginarybars]

Hello!

I am working with a database which tracks payments that constituents have made towards an upcoming tour. I have a query which shows all payments made by each constituent, and have used a crosstab in order to produce a total for each individual.

Now, my problem is that I want to create a field within the crosstab query which produces a message based on what the sum of payments is (as calculated by the query).

In the crosstab, row headings are firstname and lastname, and column headings are Payment Types (Deposit, Installment or Full Payment) and Total Of Payment Amount (which sums up the data in the rows). I tried to do a screenshot but I can't because I haven't made 10 posts yet.

Anyway, what I am trying to do is use an IIf statement to produce a message of either "Paid in Full" (if the sum of that person's payments is $3585.00) or "Balance Owing" (if it is anything else) in the next blank field. Here is what I tried to use:

IIf ([Total of Payment Amount]=3585, "Paid In Full", "Balance Owing")

This gives me an error stating that the database engine does not recognize [Total of Payment Amount] as a valid field name or expression. I've tried a number of variations on that, but I can't seem to get anything that gives me anything except an error.

Can I use this type of IIf statement in a crosstab? I worry that I'm reinventing the wheel a bit, since I'm barely an intermediate Access user. I would really appreciate any help or thoughts on this problem. Thank you!

 

[JHB]

Is it this you are seaching, (look at the attached pic)?
Remember to change the fieldname and tablename to fit your's.

TRANSFORM Sum([Payment Amount]) AS [SumOfPayment Amount]
SELECT [Firstname], [Lastname], Sum([Payment Amount]) AS [Total Of Payment Amount], IIf(Sum([Payment Amount])=3585,"Paid In Full","Balance Owing") AS Status
FROM NameAndPay
GROUP BY [Firstname], [Lastname]
PIVOT [Payment Types];

If you zip you screendump, you can send it before the 10. post.