Help with Nulling multiple fields for the same transaction date

[#1]

(REPOST - ACCIDENTALLY POSTED IN THE WRONG THREAD).

Hi,

I have a table containing a list of clients by company, branch, client number and cover number grouped together and named "Cover". The tables contains types of transactions made by each "Cover" and the date these transactions were made.

What I'm looking to do is null all transactions for the year for that specific cover if a certain transaction type was made.

For example:

Cover - 5ST-BRIA0007-0015445 has a Transaction Type "C" and "R" on 30/04/2013.

Because there is a transaction of "C" on that date, I would like all other transactions for this cover on that date/ or year (in this example "R") to be nulled.

The reason for this is that transaction "C" stands for cancellation of a policy, which means all other transactions for that policy in that year will now be void and should not be counted when working out the total premium of policies.

I have a feeling that some sort of mapping will be involved but at the moment i'm at a loss.

Any help would be greatly appreciated.

I've attached an image of the table for anyone willing to have a look.

Kind regards,

Tony

[#2]

Hi Gysbert,

I've had a go your way and it comes back with the same result as Som's.

While it successfully nulls any transaction in a year if the transaction type "C" is made (where I want to be), it also nulls other random "Cover"'s which do NOT contain transaction type "C".

What we are trying to do is find the number of active policies we have. If the policy has been cancelled ("C") then all other transactions made for that policy needs to be nulled out as it is no longer active and we do not want that data to be included in the rest of our calculations.

Using your second method while may throw out all cancelled records, still leaves other transactions for the policy for that year which will skew our data.

Appreciate the time you spent trying to assist Gysbert.

[#3]

Hi Lara,

Appreciate the time you've spent trying to help me out.

Will give it a go and let you know how it works.

Tony.

[#4]

Som,

I confirm that all "c" values in Trans_Type field is in upper case.

Values of Trans_Type field are: A , C ,D, F, J, L, N, R, V, X.

Tony