Using IIF statements to dummy columns

[Toble]

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.

[June7]

Dummy columns maybe need dummy records.

As long as there is at least one record for each subcode, a column will be generated in the CROSSTAB.

An alternative may be available in the link posted in other thread. Here it is again http://allenbrowne.com/ser-67.html#ColHead

[Toble]

Dummy columns maybe need dummy records.

As long as there is at least one record for each subcode, a column will be generated in the CROSSTAB.

An alternative may be available in the link posted in other thread. Here it is again http://allenbrowne.com/ser-67.html#ColHead

Populating dummy records is not possible as the source data is refreshed daily and the query is also run daily.
I will check the thread you provided tomorrow. Thanks

[June7]

Can use a UNION query to force the dummy records. Have a table of the 300+ subcodes. Then UNION:

SELECT Code, MasterCode FROM datatable
UN ION "A", Subcode FROM Subcodes;

Now use the UNION query for the query that calcs the GroupSeq.

[Toble]

I tried the link you provided re:Specify column headings and it worked just fine!
Thanks again June7!