Using If statements

[Joyce Carney]

I am not sure if this is the approach I should use. I do not want to have a user put in a parameter. The first thing I want to do is to create a field named "New Campaign". I have 2 fields that I want to look at: Campaign and Lead Campaign. The If statement would be Iif(Lead Campaign = "Routing Campaign", Campaign, Lead Campaign). The other thing I want to do is count the number of ids in a field named payment type. If the id is "1" then it is cash, if it is "2" then it is credit card. I want a field for each and the count. I am doing a group by on the "New Campaign"

[rpeare]

I assume you're talking about a query so you have a field with this formula:

NewCampaign: iif([Lead Campaign] = "Routing Campaign", [Campaign], [Lead Campaign])

Where [Campaign] and [Lead Campaign] are both fields on your table.
You have to encapsulate your field names in square brackets if they contain any special characters or spaces.
This is also assuming you are storing a text value in your "Lead Campaign" field instead of a foreign key to a table (autonumber PK to number FK)

For your transaction types you would have a formulae

CashTransactions: iif([PaymentType] = 1, 1, 0)
CardTransactions: iif([PaymentType] = 2, 1, 0)

You would have to use an aggregate query and use SUM in the TOTALS row of your query to get the overall count of cash/card transactions.

[Joyce Carney]

Thanks this helped