Tool Mastery | Dynamic Select

[Bonediggler]

Experts - 

Little issue here: I am extracting data from a warehouse and running it through a bunch of tools to get it shapely enough to fit into an oddly designed MS Access table.  Basically the table is a sort of giant cross tab report (don't ask me why) with - among other things - claim numbers going across the top.  For example: Name, Claim_1, Claim_2, Claim_3 up to Claim_15.

To get claim position by member I use a ranking formula, and in order to get the data ready for loading this table I use crosstab and summary tools.  The problem is there will not always be 15 claims for any one member (sometimes the max per member is only 2), in which case the summary tools will throw an error ("The field Claim_15 is not contained in the record").

Any ideas for a workaround?

Thanks!

[bsharbo]

Hello! Can you provide some example data that is fake, along with the workflow code that you are using (and your desired output if you could get your code to work).

I am not 100% sure what you are trying to accomplish so I think it will help others figure out and provide you better answers.

Example: I could be wrong, but i think it sounds like you should be pivoting all columns that have the words "Claim_" in them, that way you have one row per Name for each claim. 

Seeing where exactly things are going wrong for you will allow me to come up with a different solution (or the same one only more dynamic for you :-))

[Claje · Accepted solution]

If you can connect to the MS Access table with a second input data tool, you can do something like "SELECT TOP 0 * FROM [TABLE]" in the Access table, and then Union this to your cross-tabbed data.

This will ensure that all fields exist in your output, without creating any additional records.

[Bonediggler]

Simple.....genius.......works great!

Thanks!