Results 1 to 5 of 5
  1. #1
    Toble is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2015
    Posts
    11

    Using IIF statements to dummy columns

    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.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,633
    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
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Toble is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2015
    Posts
    11
    Quote Originally Posted by June7 View Post
    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

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,633
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Toble is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2015
    Posts
    11
    I tried the link you provided re:Specify column headings and it worked just fine!
    Thanks again June7!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Dynamic Columns - Import new columns?
    By memaxt in forum Import/Export Data
    Replies: 2
    Last Post: 06-24-2014, 06:02 AM
  2. Replies: 2
    Last Post: 05-15-2014, 06:15 AM
  3. Creating Dummy Fields in a Report
    By Whehir in forum Reports
    Replies: 2
    Last Post: 06-13-2012, 05:45 PM
  4. Replication. (Master and Dummy DBs)
    By OrangePie in forum Access
    Replies: 1
    Last Post: 07-28-2011, 03:10 PM
  5. Replies: 3
    Last Post: 04-10-2010, 10:22 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums