As a follow on from the below thread (Thanks June7 for your help!)
ttp://www.accessforums.net/queries/transform-data-pls-help-50523.html#post265179
I used the CROSSTAB query to create a data set with the following structure :
CODE SEQ1 SEQ2 SEQ3 SEQ... A 165 157 B 132 588 546 C 313 846 121 D 346 569 132 ... ...
where the value in each cell (e.g. 165,157,132 etc.) represents the subcodes for each code (A,B,C etc.).
However these subcodes also contain the mastercode (extracted from a different table) for each code.
I now need to compare the subcodes against the mastercode and hide it from the end result.
There is a maximum of 15 subcodes per code. But the data set available only has a maximum of 10 codes at present.
I used an IIF statement to compare each column against the mastercode but can only do it up to column 10 as it is the current dataset maximum.
However, future datasets could contain up to 15 subcodes.
1: IIf([TableName].[1]=[MasterCodeTable].[MasterCode]," ",[[TableName].[1]
2: IIf([TableName].[2]=[MasterCodeTable].[MasterCode]," ",[[TableName].[2]
3: ....
Assigning the above IIF statement to dummy columns creates an error as these values are not present in the table.
Thanks for your help/suggestions.![]()