Transposing columns to rows with variable columns using VBA?

[sgundlac]

Hi.
I was able to use the UNION ALL qry as suggested by PBALDY in a previous thread. But, when I have another file (like original2) that does NOT have all the columns listed in the UNION ALL qry, I get a Parameter value box asking for the missing columns when I run the qry.
Example:
original1IDDateGroupChristianJohnnySteve 18/5/2013A15 28/5/2013B3 38/5/2013C 2348/5/2013D2 358/5/2013E 5


original 2IDDateGroupChristianJohnny18/6/2013A212528/6/2013B23 38/6/2013C 2248/6/2013D22

The UNION ALL qry includes all the possible resources ( includes all the possible column fields Christan, Johnny, and Steve).
When I run the UNION ALL qry with the original2 file, An "Enter Parameter Value" box is displayed with the mssing column name "Steve".

Is there a way to Map the original2 table into a working table with all the columns, or use VBA code to construct the UNION ALL qry to only include the existing columns? My data has variable columns and I'm trying to avoid the parameter popups.

Thanks for your help.

 

[JHB]

You can use a "Dummy" field, like below:

Select .... , Null as Steve .... 
Or
Select ...., "" as Steve ....

 

[sgundlac]

Your idea is good-- but I'm not sure how to apply it. See the entire scenario attached in the word doc. I'm somewhat of a beginner- especially with VBA code, and appreciate any help you can offer